Sample Header Ad - 728x90

Relationship between /etc/localtime and Etc/UTC in PostgreSQL

0 votes
0 answers
126 views
I run our company's DBMS in containers, and when creating a container, the ansible deployment role mounts the host file /etc/localtime along the same path into the container (although, as far as I understand, the behavior would be the same if the DBMS was running as a service on the host). In previous instances, the local time zone (other than UTC) was set in postgresql.conf, but now one of the development teams has decided to work with the UTC time zone in DBMS. I created a new test instance (version 15.3) for them and specified TimeZone = 'Etc/UTC' in the configuration, and an non-obvious problem arose, which cost me a considerable number of working hours before I finally figured out what was happening: Despite the fact that the query show timezone; showed the time zone Etc/UTC, in fact all requests related to the time output (for example select current_timestamp;) worked as if the time zone from /etc/localtime was really there. If I'm through SET TimeZone TO '…'; specified other time zone except Etc/UTC — the behavior became expected, this other time zone was applied. When returning to Etc/UTC, the time zone from /etc/localtime was returned again. Etc/UTC behaves as expected only if I did not mount the file from the host at all, in which case a file with the UTC zone from the container image is used. However, I think in this case most likely the behavior is not really different, it's just that here the time zone from the file coincided with the DBMS setting. Does the Etc/UTC time zone have any special meaning (something like «consider that the system time zone is used as UTC») or is this not a feature but a bug? What would be more correct for me to do: remove the /etc/localtime mount and use Etc/UTC or mount the file and change the time zone to, for example, GMT or +00? ## Addition Queries (executed in pgcli):
> show timezone;
+----------+
| TimeZone |
|----------|
| Etc/UTC  |
+----------+

> select * from pg_timezone_names limit 2000;
+----------------------------------------+--------+------------------+--------+
| name                                   | abbrev | utc_offset       | is_dst |
|----------------------------------------+--------+------------------+--------|
| …                                      |        |                  |        |
| Etc/GMT0                               | GMT    | 0:00:00          | False  |
| Etc/Greenwich                          | GMT    | 0:00:00          | False  |
| Etc/UCT                                | CEST   | 2:00:00          | True   |
| Etc/UTC                                | CEST   | 2:00:00          | True   |
| Etc/Universal                          | CEST   | 2:00:00          | True   |
| Etc/Zulu                               | CEST   | 2:00:00          | True   |
| …                                      |        |                  |        |
+----------------------------------------+--------+------------------+--------+

> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 06:18:05.994608+02 |
+-------------------------------+

> set TimeZone to 'Etc/GMT';
> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 04:21:06.581204+00 |
+-------------------------------+
Asked by strafer (1 rep)
Apr 2, 2024, 05:16 PM
Last activity: Apr 4, 2024, 04:28 AM