Infrastructure at your Service

Daniel Westermann

Can I do it with PostgreSQL? – 10 – Timezones

This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time:

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+02:00

This is fine as I am based in Switzerland and we skipped one hour in the night from last Saturday to Sunday 🙂

How can we do something similar in PostgreSQL? To check the current time zone of your session:

([email protected][local]:4445) [postgres] > show timezone;
   TimeZone   
--------------
 Europe/Vaduz
(1 row)

Or:

([email protected][local]:4445) [postgres] > select current_setting('timezone');
 current_setting 
-----------------
 Europe/Vaduz
(1 row)

So, PostgreSQL will not show you the offset to UTC but the name of the time zone as specified by the Internet Assigned Numbers Authority (IANA). When you want to have the offset to UTC you can do something like this:

([email protected][local]:4445) [postgres] > select age(now(),now() at time zone 'UTC');
   age    
----------
 02:00:00
(1 row)

You can do it using the extract function as well:

([email protected][local]:4445) [postgres] > select extract( timezone from now() ) / 60 /60;
 ?column? 
----------
        2
(1 row)

How can you change the session time zone? One way is to set the PGTZ environment variable before starting a new session when you use a libpq client:

[email protected]:/home/postgres/ [PG3] export PGTZ=Europe/Berlin
[email protected]:/home/postgres/ [PG3] psql postgres
psql.bin (9.6.2.7)
Type "help" for help.

([email protected][local]:4445) [postgres] > show timezone;
   TimeZone    
---------------
 Europe/Berlin
(1 row)

The other way is to directly set it in the session:

Time: 1.048 ms
([email protected][local]:4445) [postgres] > set time zone 'Europe/Paris';
SET
Time: 82.903 ms
([email protected][local]:4445) [postgres] > show timezone;
   TimeZone   
--------------
 Europe/Paris
(1 row)

Of course you can also set the timezone parameter in postgresql.conf.

To get the current timestamp you can use:

([email protected][local]:4445) [postgres] > SELECT current_timestamp;
        current_timestamp         
----------------------------------
 29-MAR-17 15:41:59.203485 +02:00
(1 row)

And finally, to calculate the current time in another time zone you can do something like this:

([email protected][local]:4445) [postgres] > SELECT current_time AT TIME ZONE 'Europe/Zurich', current_time AT TIME ZONE 'US/Samoa';
      timezone      |      timezone      
--------------------+--------------------
 15:43:05.634124+02 | 02:43:05.634124-11
(1 row)

All the time zone names can be found in pg_timezone_names:

([email protected][local]:4445) [postgres] > select * from pg_timezone_names;
               name               | abbrev | utc_offset | is_dst 
----------------------------------+--------+------------+--------
 America/North_Dakota/Beulah      | CDT    | -05:00:00  | t
 America/North_Dakota/Center      | CDT    | -05:00:00  | t
 America/North_Dakota/New_Salem   | CDT    | -05:00:00  | t
 America/Argentina/Ushuaia        | ART    | -03:00:00  | f
...

Hope this helps…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure