[SOLVED] Why AT TIME ZONE increases the hours?

Why AT TIME ZONE increases the hours?

In PostgreSQL, I'm trying to convert a hour to local time zone.

My machine has UTC as timezone

show timezone;
UTC

why these queries return different answers?

SELECT '2024-07-01 00:00:00' at time zone 'PST' ;
2024-06-30 16:00:00

SELECT '2024-07-01 00:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST';
2024-07-01 08:00:00+00

thanks

Solution

set timezone = 'UTC';

SELECT '2024-07-01 00:00:00' at time zone 'UTC';
      timezone       
---------------------
 2024-07-01 00:00:00

SELECT '2024-07-01 00:00:00' at time zone 'PST' ;
      timezone       
---------------------
 2024-06-30 16:00:00

SELECT '2024-07-01 00:00:00' at time zone 'UTC'  AT TIME ZONE 'PST';
        timezone        
------------------------
 2024-07-01 08:00:00+00

In above the AT TIME ZONE 'UTC' is setting time to midnight with no time zone. Then the AT TIME ZONE 'PST' is rotating midnight time to PST and then because the 'local' timezone is UTC, the displayed time is rotated to that for display, note the +00. This moves it 8 hours ahead for the supplied offset. FYI, that offset should be PDT or better yet the timezone name 'America/Los_Angeles'.