All geek questions in one place

Custom Timezone Processing in Postgres Database on AWS RDS

My environment

I am in Paris, France ( UTC+1 or CET ).
It is 12am ( 00:00 ), we are on November 25, 2016 .
My Postgres database is hosted on Amazon web services in the eu-west-1 .

Problem

A query for current_date (or current_time ) with a specific set of time zones seems to deliver results that are not consistent with my beliefs.

In particular, the current_date query gives a different result when using the CET or UTC+1 time zone.

Example

 SET TIME ZONE 'UTC+01'; select current_date, current_time; 
  + ------------ + -------------------- +
 |  date |  timetz |
 + ------------ + -------------------- +
 |  2016-11-24 |  22: 00: 01.581552-01 |
 + --------------------------------- +

No, 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 |
 + --------------------------------- +

The correct time and date.

Question

What is happening there?
Is it too late for me and have I mixed up UTC+1 and UTC-1 or is there something more that I am missing?

+5
source share
1 answer

The problem seems unrelated to Amazon RDS: it is related to the convention used by PostgreSQL. In this case, you have the name of the time zone back. 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 west Greenwich locations. Everywhere In addition, PostgreSQL follows the ISO-8601 standard, which is positive eastward time offset from Greenwich.

But 'CET' or 'UTC-01' both still potentially erroneous for Paris because they do not accept daylight saving time rules. (DST is one of the stupidest concepts in human history.)

Paris (like most of Europe) uses CET in the winter and CEST in the summer. Testing with 'CET' will just happen in November. If you try the same thing in the summer, you will get the wrong result.

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

The current_time function respects DST rules if your time zone setting implies any. But 'UTC-01' is a simple time offset. I never use the time with time zone or current_time data type to start with. Manual again:

We do not recommend using the time with time zone (although this is supported by PostgreSQL for legacy applications and 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

on this topic:

+2
source

Source: https://habr.com/ru/post/1260313/

More articles:


All Articles