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?
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