4

How can I round a timestamp to the nearest day in postgresql. I have tried using function date_trunc('day', d_date::timestamp) but I get that is always staying in the same day.

Example 1:

date_trunc('day' , '1967-12-03 23:00:00')

Result:

1967-12-03 00:00:00.000000

Expected:

1967-12-04 00:00:00.000000

Example 2: (This one works fine)

date_trunc('day' , '1967-12-03 00:00:00')

Result:

1967-12-03 00:00:00.000000

Expected:

1967-12-03 00:00:00.000000
Improve this question

1 Answer 1

Reset to default
8

You could add 12 hours to the timestamp and then do the truncation that you're doing already.

Something like...

date_trunc('day' , timestamp '1967-12-03 23:00:00' + interval '12 hours')
Improve this answer
2
  • @S-Man I believe it does work for times less than 12:00:00. 1967-12-03 08:00:00 gives 1967-12-03 22:00:00 as an answer that is truncated to 1967-12-03 00:00:00 which is the original date rounded down.
    – Jason Hunt
    Commented Feb 3, 2021 at 11:28
  • Sry, yes my fault.
    – S-Man
    Commented Feb 3, 2021 at 11:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.