Convert_tz when original ts not in UTC

ksqlDB
1

Hi there,

I am attempting to convert some datetime values in a stream to UTC using convert_tz. These values are in PDT/PST, depending on the time of year, and no timezone information is stored with them. In general, I’m getting expected behavior when going from PDT or PST to UTC where proper offsets are applied based on the source time.

I’m running into some issues with how convert_tz behaves during the fall PDT->PST transition. In 2020, that happened on Nov 1st.

I would expect convert_tz to have to guess the proper UTC offset on any value it would attempt to convert between 2020-11-01 01:00:00 to 01:59:59, as the same hour is essentially repeated if no offset information is present.

This is fine for my purposes, and my testing so far shows that a -7 offset is applied while converting times in that range to UTC.

ksql> select CONVERT_TZ('2020-11-01T01:10:00', 'America/Los_Angeles', 'UTC') AS DATETIME_UTC from MY_STREAM emit changes limit 1;
+---------------------------------------------------------------------------------------------------------------------------------+
|DATETIME_UTC                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------+
|2020-11-01T08:10:00.000

What I’m running into now is that a datetime that should be only possible in PST (2020-11-01T02:01:00) is being converted to UTC with the -7 offset instead of the proper -8 offset:

ksql> select CONVERT_TZ('2020-11-01T02:01:00', 'America/Los_Angeles', 'UTC') AS DATETIME_UTC from MY_STREAM emit changes limit 1;
+----------------------------------------------------------------------------------------------------------------------------------------+
|DATETIME_UTC                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------+
|2020-11-01T09:01:00.000

This -7 offset continues to be applied to conversions up until 2020-11-01 09:00:00 when the -8 offset is then applied:

ksql> select CONVERT_TZ('2020-11-01T09:00:00', 'America/Los_Angeles', 'UTC') AS DATETIME_UTC from MY_STREAM emit changes limit 1;
+----------------------------------------------------------------------------------------------------------------------------------------+
|DATETIME_UTC                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------+
|2020-11-01T17:00:00.000

Is this a bug in convert_tz not knowing when PST starts in that year?

Thanks!

Erik

2

This topic was automatically closed after 30 days. New replies are no longer allowed.