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

SET TIME ZONE 'UTC+01';
select current_date, current_time;

+------------+--------------------+
| date       | timetz             |
+------------+--------------------+
| 2016-11-24 | 22:00:01.581552-01 |
+---------------------------------+

Nope, that was yesterday -- two hours ago.

---

SET TIME ZONE 'CET';
select current_date, current_time;

or

SET TIME ZONE 'Europe/Paris';
select current_date, current_time;

+------------+--------------------+
| date       | timetz             |
+------------+--------------------+
| 2016-11-25 | 00:00:01.581552-01 |
+---------------------------------+

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 1

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:

• Oddities with AT TIME ZONE and UTC offsets

• Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

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:

SELECT '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'UTC+01' AS plus_wrong
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'UTC-01' AS minus_right

     plus_wrong      |     minus_right     
---------------------+---------------------
 2016-06-05 23:00:00 | 2016-06-06 01:00:00

SELECT '2016-01-01 00:00+0'::timestamptz AT TIME ZONE 'CET'    AS cet_winter
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'CEST'   AS cest_summer
     , '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'CET'    AS cet_no_dst  -- CET wrong!

     cet_winter      |     cest_summer     |     cet_no_dst      
---------------------+---------------------+---------------------
 2016-01-01 01:00:00 | 2016-06-06 02:00:00 | 2016-06-06 01:00:00  -- wrong

SELECT '2016-06-06 00:00+0'::timestamptz AT TIME ZONE 'Europe/Paris' AS paris_summer
     , '2016-01-01 00:00+0'::timestamptz AT TIME ZONE 'Europe/Paris' AS paris_winter

 paris_summer         | paris_winter
----------------------+----------------------
 2016-06-06 02:00:00  | 2016-01-01 01:00:00  -- always right

Related:

• Ignoring time zones altogether in Rails and PostgreSQL

• Time zone names with identical properties yield different result when applied to timestamp

• Time zone storage in data type "timestamp with time zone"