Why do I get different values in postgresql when using UCT values vs timezone names?-CodePudding

If I run this query:

SELECT ('2021-11-02 08:00:00 00' AT TIME ZONE 'UCT') at TIME ZONE 'UCT 2';

It gives this result:

2021-11-02 10:00:00 00

Which seems correct to me...

However, my timezone data comes in string format, for example: "Africa/Johannesburg", which I know is on the UCT 2 timezone.

But running this query:

SELECT ('2021-11-02 08:00:00 00' AT TIME ZONE 'UCT') at TIME ZONE 'Africa/Johannesburg';

Gives this result:

2021-11-02 06:00:00 00

Which is the opposite of what I expect to happen. I know I can write code to convert the strings to a UCT value but I don't understand why the string values seems to do the opposite of using UCT values. Could someone please explain why this happens?

CodePudding user response：

Wow, there's lots to unpack here.

The main thing is that (to mis-quote Inigo Montoya) I do not think that UTC 2 means what you think it means.

One important thing here is the precise format.

You say you get this result:

2021-11-02 06:00:00 00

But What I get is:

2021-11-02 06:00:00

And that's really important because what I get doesn't include that 00. And that's because it's still 08:00 in the 00 time zone.

The AT TIME ZONE syntax says something like "What would a local at this place see the time being at this specific UTC time?". And someone in Johannesburg would see it as 10:00. Someone in London (in winter time) would see it as 06:00. But it's the same instant in time.

So the output of AT TIME ZONE doesn't usually have that time zone offset from UTC ( 00) included in it.

Your conversions with two AT TIME ZONE clauses are redundant. Because it knows it's at that time zone, and hence what the UCT time is. You'll get the same result with one AT TIME ZONE clause (try it).

The reason why you get different answers appears to be because PostgreSQL does not interpret UCT 2 as the TimeZone that is 2 hours ahead of UCT. It interprets that as you specifying a POSIX time zone definition, which could also include daylight savings time rules etc.

This page: https://www.postgresql.org/docs/current/datatype-datetime.html says PostgreSQL will accept time zones specified in 3 ways:

Full name - e.g. 'Africa/Johannesburg'
Abbreviation - e.g. 'SAST'
Posix format, e.g. 'CET-1CEST,M3.5.0,M10.5.0/3' (This is, apparently, Paris)

Actually, it doesn't include the Posix format example, that's on this page: https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

Basically, the posix format is a way of specifying the time zone rules.

Something like

[abbreviation][offset][daylight savings abbrev][DST offset][dst rules]

And so saying 'UCT 2' is probably "bad form" because you're trying to change the meaning of the abbreviation 'UTC' which has a standard usage.

You can verify that it isn't using the "UCT" part of your time zone to refer to the actual UCT time zone. If you do this:

SELECT ('2021-11-02 08:00:00 00' AT TIME ZONE 'BOB')

PostgreSQL will say it doesn't recognise time zone "BOB".

But if you do

SELECT ('2021-11-02 08:00:00 00' AT TIME ZONE 'BOB 2')

It'll happily give you 06:00:00. In fact, if you like, you can stick (almost) anything in there instead of "UCT". It doesn't get used in the calculation.

Note that the posix page I linked says

POSIX time zone specifications are inadequate to deal with the complexity of real-world time zone history, but there are sometimes reasons to use them.

Finally, 'UCT' is perfectly valid but it's doing my head in. I've only seen it as 'UTC', so TIL.

CodePudding user response：

my timezone data comes in string format, for example: "Africa/Johannesburg", which I know is on the UCT 2 timezone.

becasue postgres use POSIX-style for AT TIME ZONE

here is some description about time zone

　PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset or STDoffsetDST

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

The positive sign is used for zones west of Greenwich so that is why we might need to use it for your expected result.

SELECT ('2021-11-02 08:00:00 00' AT TIME ZONE 'UCT') at TIME ZONE 'UCT-2';