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
Last activity: Apr 4, 2024, 04:28 AM