Why does Postgres not recognize my DST?

Question

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.

Do you mean when I tried to use different timezones? I just changed the tz at session level by using SET TIME ZONE command. — Daniele Repici
– Daniele Repici, Commented Oct 4, 2016 at 10:31
@DanieleRepici your expectation is not complete, Postgres will assume timestamptz '2016-12-25 12:00:00.000 Europe/London', which is indeed 2016-12-25 12:00:00+00 (i.e. Postgres will use the time zone, not its current offset). — pozs
– pozs, Commented Oct 4, 2016 at 10:39
@pozs so basically you are saying that it does not take into account the current time to infer the correct offset in this case. Is this behaviour specified somewhere? As for the bold sentence in the quoted documentation, in order to make the conversion, Postgres should be asking itself: "ok, what is the offset for the timezone 'Europe/London'?" and it depends on the current time indeed. — Daniele Repici
– Daniele Repici, Commented Oct 4, 2016 at 10:55
@DanieleRepici yes. whenever the Postgres docs refer timezone it means a time zone, which may or may not contain DST rules. If it does, it will always taken into account. You can set explicit offsets too as time zones: then it will never use DST rules. The Time Zones section of the docs could further clarify on this. — pozs
– pozs, Commented Oct 4, 2016 at 11:13

Laurenz Albe · Accepted Answer · 2016-10-04 11:24:43Z

2

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.

answered Oct 4, 2016 at 11:24

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniele Repici Over a year ago

I've just been stuck into this since yesterday, thank you. It was so trivial :(

Collectives™ on Stack Overflow

Why does Postgres not recognize my DST?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related