5

I found the following line in my postgresql.conf:

timezone = 'CET'

And I do also get this by querying the settings:

postgres=# show timezone;
 TimeZone
----------
 CET

Anyways, we currently still have DST active and following example shows, that the server knows this (+02 is CEST):

postgres=# select now();
              now
-------------------------------
 2019-10-09 02:03:33.48477+02

But there are also time zones known to PostgreSQL, that are abbreviated by CET and do not have DST. In particular, all of the time zones with this abbreviation have offset +01:

postgres=# select * from pg_timezone_names() where abbrev = 'CET';
      name      | abbrev | utc_offset | is_dst
----------------+--------+------------+--------
 Africa/Algiers | CET    | 01:00:00   | f
 Africa/Tunis   | CET    | 01:00:00   | f

postgres=# select * from pg_timezone_abbrevs where abbrev = 'CET';
 abbrev | utc_offset | is_dst
--------+------------+--------
 CET    | 01:00:00   | f

I am now confused about this and the meaning of this abbreviation and the TimeZone setting. I expected, that every timestamp with time zone is displayed in the time zone that is set by this parameter. But obviously, this does not hold:

postgres=# select now(), now() AT TIME ZONE (SELECT setting FROM pg_settings WHERE name = 'TimeZone');
             now              |         timezone
------------------------------+---------------------------
 2019-10-09 02:03:33.48477+02 | 2019-10-09 01:03:33.48477

So finally, here are my questions:

How does this really work? Is there anywhere a setting specifying to use certain DST rules? The output I get is what I expected from a time zone like Europe/Berlin, which has DST rules. But what if I needed my database to run in real CET (so without any DST)? Or is there another configuration I did not see, which overrides the TimeZone?

Also I wonder about how AT TIME ZONE works. In the documentation, I found the following:

They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

But as my query shows, this does not really apply. There is also the following statement at the same page, that seems not to apply as well (log_timezone is also CET):

You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.

Did I mix up anything? Or are their some known bugs (and are they perhaps fixed in newer versions)? Are the docs just wrong?

My PostgreSQL version() is PostgreSQL 9.6.5, compiled by Visual C++ build 1800, 64-bit.

(Disclaimer: I am somewhat happy about what it is doing, at least apart from the "problem" with AT TIME ZONE, since I am in Germany and we have DST. But I do not understand why CET defaults to using DST and I wonder if there is a parameter to turn this off / configure it. Also I feel like there is something wrong with the AT TIME ZONE specification or the default behavior respectively.)

Islingre
  • 2,030
  • 6
  • 18
  • 3
    Downvoting without any comment does really help anyone... This is a good question and i would line to know the answer to that, too. – T_01 Oct 09 '19 at 06:51
  • Take a look at: https://stackoverflow.com/questions/52915809/postgresql-now-and-daylight-saving – ecp Oct 09 '19 at 12:29
  • I believe that until the last Sunday of October, CET (Central European Time) will only contain countries that do not adhere to daylight savings, and countries that do are listed under CEST (Central European Summer Time). Why, I am not entirely sure, but of anybody could elaborate on this I would be very interested. – Lucas Oct 09 '19 at 13:04
  • @ecp I do not see, how this question helps... And the answer (rebuilding with ```--with-system-tzdata```) does not fit for me as well. I have not changed anything about the time zone data. – Islingre Oct 09 '19 at 13:11
  • @Lucas I guess this is true. As I understand, there are DST rules defined in the time zone data and somehow managed by ```pg_timezone_names()```. Anyways, my settings are ```CET``` but do behave like ```CEST``` at the moment. Since there are (also) some time zones that are abbreviated by ```CET``` and do not have DST, I wonder how this behavior is configured (or how it is "overwritten" or things like this). – Islingre Oct 09 '19 at 13:17
  • @Islingre I would imagine that the `pg_timezone_names` view will update CEST abbreviated countries to show as CET, at least until the last Sunday of March. You could always wait until the 31st of October 2021 when Europe does away with daylight savings all together - however this still doesn't answer your question ;) – Lucas Oct 09 '19 at 14:03
  • @Lucas I guess ```pg_timezone_names()``` calculates the abbrev using the time zone data, including DST rules. Anyways, I don't feel like this function has to do anything with my questions. Thanks for your time nevertheless – Islingre Oct 09 '19 at 14:15
  • @Islingre are you aware that this is a view as well as a function? https://www.postgresql.org/docs/current/view-pg-timezone-names.html – Lucas Oct 09 '19 at 14:28
  • @Lucas Yes, I am. In the post I queried the view, while in the comments I reffered to the function (note the brackets). Anyways, the view just shows the data from the function. – Islingre Oct 09 '19 at 14:38
  • 3
    There is a timezone whose name is "CET". There are also time zones whose abbreviation is "CET". So the one named "CET" gets used in contexts that don't accept abbreviations. This is not a full explanation, but I think it explains some of things you see. – jjanes Oct 09 '19 at 15:51
  • @jjanes This is actually true.. Did not look for this, as I "classified" it as an abbreveation. I did just carefully reread the documentation on how timestamps are parsed (https://www.postgresql.org/docs/9.6/datetime-input-rules.html) and if this applies to time zone parsing as well, full names are not searched if there is neither a minus (```-```) nor a slash (```/```) nor at least 2 dots (```.```) in the string. – Islingre Oct 09 '19 at 16:11
  • Does anybody know some documentation specifying the parsing of timezones? – Islingre Oct 09 '19 at 16:50

0 Answers0