When it comes to conversions between time zones in PostgreSQL, there is already much you can do today. What PostgreSQL up to version 16 does not support, is the conversion to the local session’s time zone with ‘AT LOCAL’. Starting with PostgreSQL 17 this will be possible, as this functionality was just committed.

Let’s have a look at how you can do the conversions from one time zone to another today. A very simple example is this one:

postgres=# show timezone;
   TimeZone    
---------------
 Europe/Zurich
(1 row)

postgres=# select now();
              now              
-------------------------------
 2023-10-13 10:44:01.527876+02
(1 row)

postgres=# select now() at time zone 'UTC';
          timezone          
----------------------------
 2023-10-13 08:44:07.963283
(1 row)

postgres=# select now() at time zone 'UTC-2';
          timezone          
----------------------------
 2023-10-13 10:44:46.859132
(1 row)

This gives you the current time in ‘UTC’ and ‘UTC-2’ (which is my current time zone). A more readable format (at least for me) is to use the names of the time zones, e.g.:

postgres=# select now() at time zone 'Europe/Zurich';
          timezone          
----------------------------
 2023-10-13 10:46:18.676895
(1 row)

All the available time zone names are listed in pg_timezone_names:

postgres=# select * from pg_timezone_names limit 10;
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 GMT0                | GMT    | 00:00:00   | f
 HST                 | HST    | -10:00:00  | f
 MST                 | MST    | -07:00:00  | f
 WET                 | WEST   | 01:00:00   | t
 Turkey              | +03    | 03:00:00   | f
 Cuba                | CDT    | -04:00:00  | t
 CST6CDT             | CDT    | -05:00:00  | t
 Canada/Central      | CDT    | -05:00:00  | t
 Canada/Newfoundland | NDT    | -02:30:00  | t
 Canada/Eastern      | EDT    | -04:00:00  | t
(10 rows)

If you want to know what the current time is in “Canada/Central” this is as easy as this:

postgres=# select now() at time zone 'Canada/Central';
          timezone          
----------------------------
 2023-10-13 03:50:15.374658
(1 row)

Starting with PostgreSQL 17 you can in addition use the ‘AT LOCAL’ syntax:

postgres=# set timezone to 'Europe/Dublin';
SET
postgres=# select now() at local;
          timezone          
----------------------------
 2023-10-13 09:58:10.712968
(1 row)

postgres=# select now() at local timezone;
          timezone          
----------------------------
 2023-10-13 09:58:18.308281
(1 row)

postgres=# set timezone to 'Asia/Calcutta';
SET
postgres=# select now() at local;
         timezone         
--------------------------
 2023-10-13 14:28:39.4605
(1 row)

postgres=# select now() at local timezone;
          timezone          
----------------------------
 2023-10-13 14:28:41.811844
(1 row)

The word “timezone” is optional and can be skipped. For completeness: If you try this syntax in versions before 17, you’ll get this:

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select now() at local timezone;
ERROR:  syntax error at or near "local"
LINE 1: select now() at local timezone;
                        ^
postgres=#

Have a look at this mail thread, if you are interested in the discussion which finally resulted in this feature.

Post Views: 609
Thumbnail [60x60]
by
Daniel Westermann