In my postgresql.conf file, my default timezone is set to 'Europe/London'.
Since the DST will be switched off on 30th October, this means that now (4th October while I am writing) 'Europe/London' should correspond with '+01'.
I have a dummy table with a time column of timestamp with timezone type.
Postgres specs state that:
For
timestamp with timezone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
As for the bold sentence, if I make this insert:
INSERT INTO dummy VALUES ('2016-12-25 12:00:00.000')
I expect it to be interpreted as a 'Europe/London' timezone, thus '2016-12-25 12:00:00.000+01'.
Therefore, when I will retrieve it, I expect to be showed that value (or, at least, the equivalent '2016-12-25 11:00:00.000+00').
Instead, if I do the query
SELECT * FROM dummy
I am returned this:
|time |
|timestamp with time zone|
--------------------------
|2016-12-25 12:00:00+00 |
I can't get the reason of this behaviour. Is the DST management of 'Europe/London' timezone wrong? Am I missing something?
If I switch to any other timezone, it always works as expected.
The timestamp you enter is interpreted in the Europe/London time zone.
On the 25th of December, London will be offset 0 hours from UTC, so noon will be at 12:00 UTC.
Time zone Europe/London is not the same as time zone +01, at least not all the time.