How do I add timezones to my Datetime values?

SQL logs timestamps in two formats: timestamp and timestamptz. timestamp refers to timestamp without timezone, whereas timestamptz refers to timestamp with timezone. Benchling warehouse stores datetime data in UTC timezone.

Therefore, if you’d like to show timezones in your query, cast your timestamp value to timestamptz before converting to your desired timezone as shown below:

SELECT
timezone('US/Pacific', created_at.datetime::timestamptz) as "Created Time"
FROM org.registry_entity$raw

Another way to convert from UTC to your desired timezone is:

SELECT created_at at time zone 'UTC' at time zone 'America/New_York' 
FROM org.registry_entity$raw

You can find the list of available timezone names by querying:

SELECT name, abbrev FROM pg_timezone_names

Using the timezone name instead of the timezone abbreviation will take into account daylight savings time.

Additional resources

List of available timezones:

https://www.postgresql.org/docs/7.2/timezones.html

https://www.postgresql.org/docs/9.2/view-pg-timezone-names.html

More details on using timezones: https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/