iTranslated by AI
I wasn't entirely clear on how TimeZone behaves in PostgreSQL, so I did some research to understand it in detail.
Environment
- PostgreSQL 14
What is the TimeZone setting?
To get straight to the point: TimeZone is a session parameter. It affects the results of queries executed by the session on a per-session basis.
Quoting from the official documentation:
TimeZone (string)
Sets the time zone for displaying and interpreting time stamps.
By having the TimeZone setting, you can display and update data stored in the database with a TIME ZONE according to the session's (client's) time zone.
The most detailed explanation of TimeZone in the official documentation can be found in 8.5.3. Time Zones.
How is the TimeZone determined?
Setting at connection
It is determined in the following order:
- The TimeZone value set by
ALTER [DATABASE | USER] SET TIMEZONE TO ~ - The timezone value set in
postgresql.conf - GMT if nothing is set
Setting after connection
After a connection is established, you can change the setting using the SET command.
- Set it using
SET [session | local] timezone ~orSET [session | local] TIME ZONE ~
When a DB client specifies the TimeZone
When a DB client can specify the TimeZone, it often executes SET SESSION ~ after establishing the connection.
For example, if you specify the TimeZone in ActiveRecord within a Rails application as follows:
config.active_record.default_timezone = :utc
When the Rails application runs, the following is recorded in the PostgreSQL log:
LOG: statement: SET SESSION timezone TO 'UTC'
However, the situation is different for clients using JDBC. While I am not entirely certain about the details, it seems that a JVM property called user.timezone has an impact. Since there is no indication that a SET command is executed upon connection, I have not been able to determine exactly how the session's TimeZone is set in this mechanism.
For instance, in the case of DBeaver, a GUI DB client that uses JDBC, it defaults to the local TimeZone, but you can specify a TimeZone by adding -Duser.timezone=<timezone> to the application's launch options.
How TimeZone affects execution results
Now, let's look at how the session's TimeZone affects execution results.
First, we create a table with columns that have and do not have TIME ZONE as follows:
CREATE TABLE example (
ts1 timestamp WITHOUT TIME ZONE,
ts2 timestamp WITH TIME ZONE,
time1 time WITHOUT TIME ZONE,
time2 time WITH TIME ZONE
);
Results of registration and retrieval
We register data with the session's timezone set to UTC.
SET SESSION timezone TO 'UTC';
INSERT INTO example (ts1, ts2, time1, time2) VALUES (
'2020-01-01 00:00:00',
'2020-01-01 00:00:00',
'00:00:00',
'00:00:00'
);
Retrieving the results shows that they match the data registered.
SELECT * FROM example;
ts1 | ts2 | time1 | time2
---------------------+------------------------+----------+-------------
2020-01-01 00:00:00 | 2020-01-01 00:00:00+00 | 00:00:00 | 00:00:00+00
Next, we retrieve it with the session's timezone set to Asia/Tokyo.
SET SESSION timezone TO 'Asia/Tokyo';
Only the timestamp column with TIME ZONE displays the time adjusted for the session's time zone.
SELECT * FROM example;
ts1 | ts2 | time1 | time2
---------------------+------------------------+----------+-------------
2020-01-01 00:00:00 | 2020-01-01 09:00:00+09 | 00:00:00 | 00:00:00+00
The inverse is also true; data registered with timezone=Asia/Tokyo is registered with +09, so if you retrieve it with timezone=UTC, the value shifted by +00 is displayed.
SET SESSION timezone TO 'Asia/Tokyo';
INSERT INTO example (ts1, ts2, time1, time2) VALUES (
'2020-01-01 00:00:00',
'2020-01-01 00:00:00',
'00:00:00',
'00:00:00'
);
SELECT * FROM example;
ts1 | ts2 | time1 | time2
---------------------+------------------------+----------+-------------
2020-01-01 00:00:00 | 2020-01-01 00:00:00+09 | 00:00:00 | 00:00:00+09
SET SESSION timezone TO 'UTC';
SELECT * FROM example;
ts1 | ts2 | time1 | time2
---------------------+------------------------+----------+-------------
2020-01-01 00:00:00 | 2019-12-31 15:00:00+00 | 00:00:00 | 00:00:00+09
AT TIME ZONE
The results of AT TIME ZONE differ depending on whether the timestamp type has TIME ZONE or not.
SET SESSION timezone TO 'UTC';
INSERT INTO example (ts1, ts2) VALUES (
'2020-01-01 00:00:00',
'2020-01-01 00:00:00'
);
SELECT * FROM example;
ts1 | ts2 | time1 | time2
---------------------+------------------------+----------+-------------
2020-01-01 00:00:00 | 2020-01-01 00:00:00+00 | 00:00:00 | 00:00:00+00
SELECT ts1 AT TIME ZONE 'Asia/Tokyo',
ts1 AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo',
ts2 AT TIME ZONE 'Asia/Tokyo'
FROM example;
timezone | timezone | timezone
------------------------+---------------------+---------------------
2019-12-31 15:00:00+00 | 2020-01-01 09:00:00 | 2020-01-01 09:00:00
- When AT TIME ZONE is specified for a column without TIME ZONE, it displays the value as if the column were in the specified TIME ZONE.
- When two AT TIME ZONE clauses are specified for a column without TIME ZONE, the first AT TIME ZONE sets the TIME ZONE of the column (making it equivalent to a column with TIME ZONE), and the second shifts the column value to the specified TIME ZONE for display.
- When AT TIME ZONE is specified for a column with TIME ZONE, it shifts the column to the specified TIME ZONE for display.
I'm starting to get confused, so I'll stop here.
Summary
- When connecting to a DB from an application or DB client, specify the TimeZone explicitly.
- Since the behavior of functions that convert TimeZone changes based on the session's timezone and the presence of a TIME ZONE in the column, verify that the behavior matches your expectations.
Discussion