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:

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

Or:

(postgres@[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:

(postgres@[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:

(postgres@[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:

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

(postgres@[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
(postgres@[local]:4445) [postgres] > set time zone 'Europe/Paris';
SET
Time: 82.903 ms
(postgres@[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:

(postgres@[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:

(postgres@[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:

(postgres@[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…