Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Peculiar time zone handling in a Postgres database

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?

like image 512
ccjmne Avatar asked Nov 24 '16 23:11

ccjmne


People also ask

What is timezone in PostgreSQL?

PostgreSQL assumes your local time zone for any type containing only date or time. All timezone-aware dates and times are stored internally in UTC . They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

How does Postgres store timestamp with timezone?

The TIMESTAMP (also known as TIMESTAMP WITHOUT TIME ZONE ) and TIMESTAMPTZ (also known as TIMESTAMP WITH TIME ZONE ) types stored as a 64-bit integer as a microsecond offset since 1970-01-01 in CRDB and as a 64-bit integer microsecond offset since 2000-01-01 in PostgreSQL (by default).


1 Answers

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"

like image 188
Erwin Brandstetter Avatar answered Oct 04 '22 03:10

Erwin Brandstetter