Convert UTC based on a separate time zone column - Postgres

1
votes

I have a table which has:

  • Source date column
  • Source timestamp (30 minute interval, a string)
  • The datetime UTC timestamptz column

  • The 'reporting' time zone (how the time zone should be displayed eventually)

    date      interval     datetime_utc     time_zone

    2018-05-11 22:30 2018-05-10 03:30:00 America/New_York

Is there an efficient way to convert the datetime_utc column to the time zone indicated in the same row within a query or a view? I was able to do this using Python, but I would like to know how to do it directly in my query.

This seems to work - I am able to refer to the column name and it converts it accurately based on the value found in that row.

 (interval_timestamp_utc :: TIMESTAMPTZ AT TIME ZONE time_zone) :: DATE AS "Reporting Date",

 to_char((interval_timestamp_utc :: TIMESTAMPTZ AT TIME ZONE time_zone), 'HH24:MI') AS "Reporting Interval",
1
postgresqldatetimetimezone
select '2018-05-10 03:30:00'::timestamp at time zone 'America/New_York'; ? Look also pg_catalog.pg_timezone_abbrevs and pg_catalog.pg_timezone_names tables. - Abelisto
Each row has a different time zone though - I am not selecting a specific row. I am querying a full calendar day which has about 1 - 2 million rows and I am converting the source time zone to a 'display' time zone used for visualizations. - trench

1 Answers

2
votes

I think the AT TIME ZONE construct should be what you're looking for. In your case, SELECT TIMESTAMP WITH TIME ZONE datetime_utc AT TIME ZONE time_zone should give you the timestamp rendered without an offset for that specific time zone.

See https://www.postgresql.org/docs/9.6/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT for more details.