PostgreSQL Correct Timezones Handling Cookbook

1) All data types in tables, that include time component (time and timestamp) - are created without time zone information. For example:

create table my_table (    ts timestamp without time zone,    t  time without time zone);-- or, shorter --create table my_table (    ts timestamp,    t  time);

2) All parameters (sent from client) or variables (declared for a script block), that include time component - are created with time zone information. For example:

declare    _timestamp timestamp with time zone = now();-- or, shorter --declare    _timestamp timestamptz = now();

3) All inserts into tables (time types don't have a time zone) from parameters or variables (time types have a time zone) - are converted into the UTC timezone first. For example:

declare    _timestamp timestamp with time zone = now();begin    insert into my_table (ts) values (_timestamp at time zone 'utc');-- or, shorter --declare    _timestamp timestamptz = now();begin    insert into my_table values (_timestamp at time zone 'utc');

4) Converting a time data type to a user-specific time zone (to be shown on a UI for example), time data must be converted into a UTC and then into a user-specific timezone. For example:

-- Zagreb timezoneselect ts at time zone 'utc' at time zone 'Europe/Zagreb'from my_table;-- New York timezoneselect ts at time zone 'utc' at time zone 'America/New_York'from my_table;-- etc...

Notes:

• User timezone can be passed as a parameter or saved in a user table/profile.

• User timezone can be fetched from the browser by using the following JavaScript expression:
  

Intl.DateTimeFormat().resolvedOptions().timeZone

• To ensure that the user timezone is a valid timezone, ensure that exists in the following PostgreSQL query:

select name from pg_timezone_names