Peculiar time zone handling in a Postgres database

Question:

My environment

I’m in Paris, France (UTC+1 or CET).
It’s 12am (00:00), we’re on the 25th of November 2016.
My Postgres database is hosted on Amazon Web Services (AWS RDS) in the eu-west-1 region.

The issue

Querying for the current_date (or current_time) with a specific time zone set seems to deliver results that aren’t consistent with… my beliefs.

In particular, querying for the current_date yields a different result when using the CET time zone or the UTC+1 one.

Example

Nope, that was yesterday — two hours ago.


or

There’s the correct time and date.

Question

What’s going on there?
Is it too late for me and I mixed up UTC+1 and UTC-1 or is there something bigger that I’m overlooking?
Does AWS RDS have a role in this?

Answer:

The issue seems unrelated to Amazon RDS: it has to do with the convention used by PostgreSQL. In this case, you do have the time zone name backwards. You mean 'UTC-01' where you write 'UTC+01'.
From the manual:

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.

So time zone string used for SET TIME ZONE (and the display of SHOW timezone, accordingly) or the AT TIME ZONE construct use the opposite sign of what’s displayed in timestamp (with time zone) literals! That’s a very unfortunate disagreement between ISO and SQL standard on the one hand and POSIX on the other. (I think POSIX is to blame.) See:

But 'CET' or 'UTC-01' are both still potentially wrong for Paris because they are not taking rules for daylight saving time into account.
(DST is one of the most moronic concepts in the history of mankind.)
Paris (like most of Europe) uses CET during winter and CEST during summer. Your tests with 'CET' just happen to work in November. If you try the same in the summer time, you get the wrong result.

To be on the safe side, always use the time zone name 'Europe/Paris', which considers DST rules. The call is more expensive.

The function current_time takes DST rules into account if your time zone setting implies any. But 'UTC-01' is a plain time offset. I never use the data type time with time zone or current_time to begin with. The manual once more:

We do not recommend using the type time with time zone (though it is
supported by PostgreSQL for legacy applications and for compliance
with the SQL standard)

Consider:

Related:

Leave a Reply