CODEWITHSUNDEEP
postgresqltimestamp
Wayne Werner
Wayne Werner

Reputation: 51807

Why does postgres date_trunc return the hour 24?

Exactly what the question says.

mydb=> select '2016-01-03 24:00'::timestamp;
      timestamp
---------------------
 2016-01-04 00:00:00
(1 row)

That's what I expected.

mydb=> select date_trunc('seconds', '2016-01-03 23:59.9999999999'::timestamp);
     date_trunc
---------------------
 2016-01-03 00:24:00
(1 row)

Um. Wait, what?

Upvotes: 1

Views: 238

Answers (1)

schesis
schesis

Reputation: 59128

It has nothing to do with date_trunc ... once you introduce the decimal point, 23:59.9999999999 is being interpreted as minutes and seconds rather than hours and minutes.

Without decimal point

db=# select '2016-01-03 23:59'::timestamp;
      timestamp      
---------------------
 2016-01-03 23:59:00
(1 row)

With decimal point

db=# select '2016-01-03 23:59.9999999'::timestamp;
      timestamp      
---------------------
 2016-01-03 00:24:00
(1 row)

It's understandable, given what you were expecting to get back, but you seem to have misread 24 minutes as 24 hours in the result here.

As a side note, the rounding kicks in once you go past six digits (i.e. microseconds) after the decimal place:

db=# select '2016-01-03 23:59.999999'::timestamp;
         timestamp          
----------------------------
 2016-01-03 00:23:59.999999
(1 row)

Upvotes: 4

Related Questions