1

I am doing some timestamp conversion from UTC to Eastern Standard Time but found something puzzling on the "Spring forward" hour which happened on 3/12/2023 this year. As you can see, the conversion to Easter Prevailing Time using 'US/Eastern' worked all fine. However, in all three versions that I tried to convert to Eastern Standard Time failed for row 8. Row 8 is supposed to be '2023-03-12 02:00:00.000' at Eastern Standard Time (UTC-5). Instead, it showed as '2023-03-12 03:00:00.000'. But for hour before and after that row, the conversions were done correctly. Not sure if I did something wrong or understood something wrong or is this a bug? Thank you for your help on this.

select start_time_utc as utc 
    , CAST(start_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern' AS timestamp) AS east_prevailing
    , CAST(start_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'EST' AS timestamp) AS eastern_stabdard_v1
    , CAST(start_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'Etc/GMT+5' AS timestamp) AS eastern_stabdard_v2  
    , CAST(start_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Cancun' AS timestamp) AS eastern_stabdard_v3 
from public.crr_obligation_hourly
where unit_id =1 and extract(month from start_time_utc)=3 and extract(day from start_time_utc)=12
order by 1

enter image description here

Update: This issue was actually caused by DBeaver. I will post my answer below.

sguo
  • 125
  • 1
  • 10
  • There is no `2023-03-12 02:00:00.000' at Eastern Standard Time (UTC-5)`. The purpose of DST is to change that to `2023-03-12 03:00:00-04`. – Adrian Klaver Mar 13 '23 at 18:03
  • I don't agree that "there is no 2023-03-12 02:00:00.000' at Eastern Standard Time (UTC-5)". For a place that doesn't adopt daylight saving time (such as American/Cancun or Eastern Standard Time as its name suggests), of course there is '2023-03-12 02:00:00.000' – sguo Mar 13 '23 at 18:10
  • I did not pay attention to the other time zones, still: `select ('2023-03-12 07:00:00'::timestamp at time zone 'UTC') at time zone 'America/Cancun'; 2023-03-12 02:00:00`. Postgres version 14.7, `show timezone; US/Pacific`. So something else is messing with your results. I'm guessing whatever client you are using to display them. What is that? – Adrian Klaver Mar 13 '23 at 18:21
  • @AdrianKlaver, thank you for checking. I ran select ('2023-03-12 07:00:00'::timestamp at time zone 'UTC') at time zone 'America/Cancun'; and still got '2023-03-12 03:00:00.000'. I asked my Admin and the version of PostgreSQL version14.5 and the system local time zone is UTC. I ran the query using DBeaver version 22.0.5. Thanks. – sguo Mar 13 '23 at 18:32
  • I see `DBeaver` and I expect issues. Try the query in `psql`. – Adrian Klaver Mar 13 '23 at 18:39
  • I could be mistaken, but it looks like the problem is that you are casting back to `timestamp` (without time zone), and then your local time zone's DST rules are being applied for display. Is there a reason you are casting back to `timestamp` instead of leaving it as a `timestamp with time zone`? – Matt Johnson-Pint Mar 13 '23 at 23:14
  • @MattJohnson-Pint, `select CAST('2023-03-12 07:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/Cancun' AS timestamp);` yields `03/12/2023 02:00:00`. And I am doing this in `show timezone; US/Pacific`. Using `timestamptz` leads to `select CAST('2023-03-12 07:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/Cancun' AS timestamptz); 03/12/2023 03:00:00 PDT`. For more info see [AT TIME ZONE](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT). – Adrian Klaver Mar 13 '23 at 23:36

1 Answers1

0

Thanks Adrian Klaver for helping. Indeed it was caused by DBeaver. After I set my DBeaver time zone to UTC, everything looks good. My default DBeaver time zone was US/Eastern, in that time zone, "2023-03-12 02:00:00.000" is not a valid time and it seemed DBeaver automatically forced changes on it, which I don't think is a good practice. See this post for details on how to set DBeaver time zone.

sguo
  • 125
  • 1
  • 10
  • It is the only thing it could do with that time zone setting. There is no `2023-03-12 02:00:00.000` in `US/Eastern`. You go from `2023-03-12 01:59:00.000 EST` to `2023-03-12 03:00:00.000 EDT`. – Adrian Klaver Mar 14 '23 at 15:34