You can translate the question and the replies:

UTC date time field conversion - add hour VERSUS convert time zone - yield different result over daylight saving dates.

Hi, Need to understand why the two do not produce the same values when a UTC date/time field crosses over a change of daylight savings on lst March the 12th. Thanks. VQL SELECT "Event Time UTC" AS "A" ,formatdate('HH:mm:ss', convert_timezone('UTC', 'US/Mountain', "Event Time UTC")) AS "b" ,convert_timezone('UTC', 'US/Mountain', "be_BadgeReader"."Event Time UTC") AS "C" AS "Event Time Mountain" ,formatdate('HH:mm:ss', addhour("Event Time UTC", -6)) AS "D" FROM "MPA-Sensitive"."be_BadgeReader" AS "be_BadgeReader" WHERE ("Event Time UTC" > TIMESTAMP '2023-03-01 00:00:00.000' and "Event Time UTC" < TIMESTAMP '2023-04-01 00:00:00.000' ) and "Aero ID" IN ('23583') OUTPUT A---------------------B---------C---------------------D 3/9/2023 14:23 7:23:32 3/9/2023 7:23 **8:23:32** 3/9/2023 14:24 7:24:09 3/9/2023 7:24 **8:24:09** 3/23/2023 13:34 7:34:32 3/23/2023 7:34 7:34:32 3/23/2023 13:35 7:35:12 3/23/2023 7:35 7:35:12
UTC; addhour; convert_timezone
user
19-07-2023 10:49:41 -0400
code

1 Answer

Hi, According to [Daylight Saving Time](https://www.timeanddate.com/time/change/@5334727) for Mountain Time Zone in 2023, it seems that Daylight Saving Time started only on 12 March 2023. By default, Mountain Time is -7 from UTC timezone (UTC -7). Thus, when converting between “Event Time UTC” to UTC time for columns B and C, 7 hours was subtracted off to Event Time to compute the UTC time. In the case of column D, addhour(“Event Time UTC”, -6) implied that 6 hours was subtracted off the Event Time instead, thus resulting in the discrepancy. Hope this helps!
Denodo Team
20-07-2023 04:01:20 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here