PostgreSQL timezones ¶

Description ¶

The TimeZone configuration parameter can be set in the file postgresql.conf , or in any of the other standard ways described in Chapter 19 .

There are also some special ways to set it:

The SQL command SET TIME ZONE sets the time zone for the session.

This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

Dont do this ¶

https://wiki.postgresql.org/wiki/Don’t_Do_This#Date.2FTime_storage

Don’t use CURRENT_TIME ¶

Don’t use the CURRENT_TIME function. Use whichever of these is appropriate:

CURRENT_TIMESTAMP or now() if you want a timestamp with time zone,
LOCALTIMESTAMP if you want a timestamp without time zone,
CURRENT_DATE if you want a date,
LOCALTIME if you want a time

Be Aware of Timezones by Haki Benita ¶

Avoid Transformations on Indexed Fields by Haki Benita ¶

Use BETWEEN Only For Inclusive Ranges ¶

A common mistake I see very often is when filtering a date range using BETWEEN:

            SELECT *
FROM sales
WHERE created BETWEEN '2019-01-01' AND '2020-01-01';

           

Intuitively, you might think this query is fetching all the sales in 2019, but in fact, it’s fetching all the sales made in 2019 and the first day of 2020.

BETWEEN is inclusive , so the query above is equivalent to this query:

            SELECT *
FROM sales
WHERE created >= '2019-01-01'
AND created <= '2020-01-01';

           

To filter results in 2019 you can either write this:

            SELECT *
FROM sales
WHERE created BETWEEN '2019-01-01' AND '2019-12-31';

           

Or better yet:

            SELECT *
FROM sales
WHERE created >= '2019-01-01'
AND created < '2020-01-01';

           

Using BETWEEN incorrectly might produce overlapping results, for example, counting sales twice in two different periods.

PostgreSQL Data Types: Date, Timestamp, and Time Zones by Tapoueh ¶

tz.sql ¶

            begin;

drop table if exists tstz;

create table tstz(ts timestamp, tstz timestamptz);

set timezone to 'Europe/Paris';
select now();
insert into tstz values(now(), now());

set timezone to 'Pacific/Tahiti';
select now();
insert into tstz values(now(), now());

set timezone to 'Europe/Paris';
table tstz;

set timezone to 'Pacific/Tahiti';
table tstz;

commit;

           

In this script, we play with the client’s setting timezone and change from a French value to another French value, as Tahiti is an island in the Pacific that is part of France.

Here’s the full output as seen when running this script, when launched with:

            psql -d db_intranet -U intranet -a -f tz.sql.rst:

           

            begin;
BEGIN
drop table if exists tstz;
DROP TABLE
create table tstz(ts timestamp, tstz timestamptz);
CREATE TABLE
set timezone to 'Europe/Paris';
SET
select now();
              now              
-------------------------------
 2020-04-30 18:35:12.909262+02
(1 ligne)

insert into tstz values(now(), now());
INSERT 0 1
set timezone to 'Pacific/Tahiti';
SET
select now();
              now              
-------------------------------
 2020-04-30 06:35:12.909262-10
(1 ligne)

insert into tstz values(now(), now());
INSERT 0 1
set timezone to 'Europe/Paris';
SET
table tstz;
             ts             |             tstz              
----------------------------+-------------------------------
 2020-04-30 18:35:12.909262 | 2020-04-30 18:35:12.909262+02
 2020-04-30 06:35:12.909262 | 2020-04-30 18:35:12.909262+02
(2 lignes)

set timezone to 'Pacific/Tahiti';
SET
table tstz;
             ts             |             tstz              
----------------------------+-------------------------------
 2020-04-30 18:35:12.909262 | 2020-04-30 06:35:12.909262-10
 2020-04-30 06:35:12.909262 | 2020-04-30 06:35:12.909262-10
(2 lignes)

commit;
COMMIT

           

First, we see that the now() function always returns the same timestamp within a single transaction. If you want to see the clock running while in a transaction, use the clock_timestamp() function instead.

Then, we see that when we change the timezone client setting, PostgreSQL outputs timestamps as expected, in the selected timezone.

If you manage an application with users in different time zones and you want to display time in their own local preferred time zone, then you can set timezone in your application code before doing any timestamp related processing, and have PostgreSQL do all the hard work for you.

Finally, when selecting back from the tstz table, we see that the column tstz realizes that both the inserted values actually are the same point in time, but seen from different places in the world, whereas the ts column makes it impossible to compare the entries and realize they actually happened at exactly the same time.

As said before, even when using timestamps with time zone, PostgreSQL will not store the time zone in use at input time, so there’s no way from our tstz table to know that the entries are at the same time but just from different places.

The opening of this section links to The Long, Painful History of Time , and if you didn’t read it yet, maybe now is a good time.

Several options are available to input timestamp values in PostgreSQL. The easiest is to use the ISO format, so if your application’s code allows that you’re all set.

In the following example we leave the time zone out, as usually, it’s handled by the timezone session parameter, as seen above.

If you need to, of course, you can input the time zone in the timestamp values directly:

            select timestamptz '2017-01-08 04:05:06',
       timestamptz '2017-01-08 04:05:06+02';

At insert or update time, use the same literal strings without the type decoration: PostgreSQL already knows the type of the target column, and it uses that to parse the values literal in the DML statement.

Some application use-cases only need the date.

Then use the date data type in PostgreSQL. It is of course then possible to compare a date and a timestamp with time zone in your SQL queries, and even to append a time offset on top of your date to construct a timestamp.

Table pg_timezone_names ¶

           select * from pg_timezone_names order by name asc;

          

           name                            |abbrev|utc_offset|is_dst|
Africa/Abidjan                  |GMT   |  00:00:00|false |
Africa/Accra                    |GMT   |  00:00:00|false |
Africa/Addis_Ababa              |EAT   |  03:00:00|false |
Africa/Algiers                  |CET   |  01:00:00|false |
Africa/Asmara                   |EAT   |  03:00:00|false |
Africa/Asmera                   |EAT   |  03:00:00|false |
Africa/Bamako                   |GMT   |  00:00:00|false |
Africa/Bangui                   |WAT   |  01:00:00|false |
Africa/Banjul                   |GMT   |  00:00:00|false |
Africa/Bissau                   |GMT   |  00:00:00|false |

          

Setting the timezone parameter (please use UTC timezone) ¶

show timezone; ¶

           show timezone;

          

           TimeZone    |
UTC|

select clock_timestamp(); ¶

           select clock_timestamp();

          

           clock_timestamp    |
2020-04-30 16:41:01|

SELECT now() ¶

           SELECT now() AT TIME ZONE current_setting('TimeZone');
SELECT now() AT TIME ZONE 'Europe/Paris';
SELECT now() AT TIME ZONE 'UTC';

          

SELECT now() AT TIME ZONE current_setting(‘TimeZone’); ¶

            SELECT now() AT TIME ZONE current_setting('TimeZone');

           

SELECT now() AT TIME ZONE ‘Europe/Paris’; ¶

            SELECT now() AT TIME ZONE 'Europe/Paris';

           

SELECT now() AT TIME ZONE ‘UTC’; ¶

            SELECT now() AT TIME ZONE 'UTC';

           