CODEWITHSUNDEEP
sqlpostgresqldate
César Castro Aroche
César Castro Aroche

Reputation: 775

How to round timestamp to nearest day with postgresql

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

Upvotes: 4

Views: 1991

Answers (1)

Jason Hunt
Jason Hunt

Reputation: 384

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')

Upvotes: 9

Related Questions