Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

More than 24 hours in a day in postgreSQL

Assuming I have this schema:

create table rental (
    id           integer,
    rental_date  timestamp,
    customer_id  smallint,
    return_date  timestamp,
);

Running this query returns strange results:

select customer_id, avg(return_date - rental_date) as "avg"
from rental
group by customer_id
order by "avg" DESC

It displays:

customer_id|avg_rent_duration     |
-----------|----------------------|
        315|     6 days 14:13:22.5|
        187|5 days 34:58:38.571428|
        321|5 days 32:56:32.727273|
        539|5 days 31:39:57.272727|
        436|       5 days 31:09:46|
        532|5 days 30:59:34.838709|
        427|       5 days 29:27:05|
        555|5 days 26:48:35.294118|
...

599 rows

Why is there values like 5 days 34:58:38, 5 days 32:56:32 and so on? I thought there where only 24 hours in a day, maybe I'm wrong.

EDIT

Demo here: http://sqlfiddle.com/#!17/caa7a/1/0

Sample data:

insert into rental (rental_date, customer_id, return_date)
values
('2007-01-02 13:10:06', 1, '2007-01-03 01:01:01'),
('2007-01-02 01:01:01', 1, '2007-01-09 15:10:06'),
('2007-01-10 22:10:06', 1, '2007-01-11 01:01:01'),
('2007-01-30 01:01:01', 1, '2007-02-03 22:10:06');
like image 520
rap-2-h Avatar asked Sep 06 '19 12:09

rap-2-h


People also ask

How much data is too much for Postgres?

PostgreSQL normally stores its table data in chunks of 8KB. The number of these blocks is limited to a 32-bit signed integer (just over two billion), giving a maximum table size of 16TB.

Is Postgres good for large data?

You should have no problem using Postgres for reporting and analytics. Postgres generally provides fast and stable query performance and can quickly access up to a terabyte on a single instance. On the flipside, if you have a smaller (or growing) team in need of big data insights, Postgres isn't a cure-all.

What does $$ mean in PostgreSQL?

A dollar sign ($) followed by digits is used to represent a positional parameter in the body of a function definition or a prepared statement. In other contexts the dollar sign may be part of an identifier or a dollar-quoted string constant.

What is xmin and xmax in PostgreSQL?

Tuple header To this end, each version is labeled with its effective «time» ( xmin ) and expiration «time» ( xmax ). Quotation marks denote that a special incrementing counter is used rather than the time itself. And this counter is the transaction identifier.


1 Answers

You have to adjust interval with justify_interval() function:

select customer_id, justify_interval(avg(return_date - rental_date)) as "avg"
from rental
group by customer_id
order by "avg" DESC;

See official doc:

Adjust interval using justify_days and justify_hours, with additional sign adjustments

Still, it does not explains why the result of the operation is that weird without using justify_interval() (in other words, why we have to apply this function)

Note: thanks to @a_horse_with_no_name for their comment

like image 156
rap-2-h Avatar answered Sep 28 '22 08:09

rap-2-h