DEV Community 👩‍💻👨‍💻

vbilopav
vbilopav

Posted on

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
);
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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 timezone
select ts at time zone 'utc' at time zone 'Europe/Zagreb'
from my_table;

-- New York timezone
select ts at time zone 'utc' at time zone 'America/New_York'
from my_table;

-- etc...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • To ensure that the user timezone is a valid timezone, ensure that exists in the following PostgreSQL query:
select name 
from pg_timezone_names
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

🌚 Life is too short to browse without dark mode