PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres date and time confusion

Why do these two statements produce different dates?

select 'test 1', date_trunc('day', current_date at time zone 'utc' )
union
select 'test 2', date_trunc('day',current_timestamp  at time zone 'utc' )

As I type (2:20pm local time, UK) the first statement tells me that today is 16th May (yesterday), the 2nd tells me 17th May.

Both current_date and current_timestamp should be providing values for right now, and I understand from the documentation that current basically means local, and for me, now there’s only one hour difference between local and UTC.

What have I missed?

Tags:

2

Answers


  1. 0

    Try this. Current_date gives you the date with no time (start of the day), if you truncate it round it do ‘day’, well today is not over yet I guess so it rounds to yesterday.

    https://mode.com/blog/date-trunc-sql-timestamp-function-count-on

    select current_date, 'test 1', date_trunc('day', current_date at time zone 'utc-3' )
union
select current_timestamp, 'test 2', date_trunc('day', current_timestamp at time zone 'utc' )
    Login or Signup to reply.
  2. 0

    current_date is 2024-05-17 (notice that there is no time)

    When you set its timezone this date is implicit cast to timestamp:

    2024-05-17 00:00:00

    Then the time is shifted to UTC, which for UK DST time means that 1 hour needs to be subtracted, so you get

    2024-05-16 23:00:00

    At last when you call date_trunc time is truncated and you get

    2024-05-16 00:00:00

    For current_timestamp it would be respectively:

    2024-05-17 14:20:00

    2024-05-17 13:20:00

    2024-05-17 00:00:00

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search