Converting timezone sql

Question

Using the following query to convert the timezone. My table was originally in PDT, and I need to convert it to Eastern time.

SELECT DATE_FORMAT(
  CAST(
    CONCAT(
      CAST(year as VARCHAR)
      '-',
      CAST(month as VARCHAR)
      '-',
      CAST(day as VARCHAR),
      ' ',
      LPAD(CAST(hour as VARCHAR), 2, '0'), ':00:00') as timestamp) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York', '%Y-%m-%d %H:%i:%s') as t 
from table
where DATE_FORMAT(
  CAST(
    CONCAT(
      CAST(year as VARCHAR)
      '-',
      CAST(month as VARCHAR)
      '-',
      CAST(day as VARCHAR),
     'America/Los_Angeles' AT TIME ZONE 'America/New_York', '%Y-%m-%d') >= '2023-07-05'

However this is only outputing time >= '2023-07-05 20:00:00'. What was wrong?

can you provide actual data, so that someone can reproduce your problem — nbk, Jul 07 '23 at 22:02
Please add the sample date on which the query can be run on. Also why do you have two sequential `AT TIME ZONE`'s - `AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York'`? — Guru Stron, Jul 07 '23 at 22:45
seems like you need to lpad month and day too. but provide the year/month/day/hour that you have stored so we can try it out ourselves. — ysth, Jul 08 '23 at 00:57
Please show us some sample rows from the table, with the _exact_ contents of each field. — John Rotenstein, Jul 08 '23 at 03:56
@JohnRotenstein it seems that there is actually enough info. At least if my assumption is correct =) — Guru Stron, Jul 08 '23 at 08:03
What is the format of the input columns -- are they numbers or are they varchar? What do you mean by "only outputing time >= '2023-07-05 20:00:00'" -- please show us what values you want as output and the matching values in the table that should produce this output. — John Rotenstein, Jul 08 '23 at 09:04

score 0 · Answer 1 · answered Jul 08 '23 at 08:01

However this is only outputing time >= '2023-07-05 20:00:00'. What was wrong?

I think it is not 2023-07-05 20:00:00 but 2023-07-05 20:00:00, note the following - 'America/New_York' is 4 hours behind UTC, so:

SELECT date_format(timestamp '2023-07-05 00:00' AT TIME ZONE 'America/New_York', '%Y-%m-%d %H:%i:%s');

Will result in ('UTC' is the default timezone in Athena AFAIK):

        _col0
---------------------
 2023-07-04 20:00:00

If your data is originally in some timezone then you need to parse it including this information:

-- sample data
with dataset(year, month, day, hour) as (
    values  (2023, 7, 4, 23),
            (2023, 7, 5, 1),
            (2023, 7, 5, 2),
            (2023, 7, 5, 3),
            (2023, 7, 5, 4)
)

-- query
select PARSE_DATETIME(dt, 'yyyy-M-d H') at time zone 'America/New_York' without_tz,
       PARSE_DATETIME(dt || ' America/New_York', 'yyyy-M-d H ZZZ') at time zone 'America/New_York' with_tz
from (
    SELECT CONCAT(
          CAST(year as VARCHAR),
          '-',
          CAST(month as varchar),
          '-',
          cast(day as varchar),
          ' ',
          cast(hour as varchar)) dt
    FROM dataset);

Which results in:

without_tz	with_tz
2023-07-04 19:00:00.000 America/New_York	2023-07-04 23:00:00.000 America/New_York
2023-07-04 21:00:00.000 America/New_York	2023-07-05 01:00:00.000 America/New_York
2023-07-04 22:00:00.000 America/New_York	2023-07-05 02:00:00.000 America/New_York
2023-07-04 23:00:00.000 America/New_York	2023-07-05 03:00:00.000 America/New_York
2023-07-05 00:00:00.000 America/New_York	2023-07-05 04:00:00.000 America/New_York

So obviously your filter will fail to produce correct results on the without_tz column (also note that cast(... as timespan) seems to ignore timezone info, while literal - timezone ... will process it).

P.S.

AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York' is kind of pointless - it will convert to one timezone and then to another, so basically it should be the same as converting to the final timezone.

P.S. already not near computer - based on provided info I don't see need for timezone handling in the filter - just turn your year-month-day info into date and filter using something `>= date '2023-05-07'`. — Guru Stron, Jul 08 '23 at 08:11

Converting timezone sql

1 Answers1