Server information

show timezone;

Europe/Paris

I used to cast character strings in timestamp without time zone using:

select '2003-04-30 02:01:47'::timestamp

2003-04-30 02:01:47

Generally CAST works well, but I had recently discovered that PostgreSQL doesn't render the expected values in some specifics cases. Indeed for each summer time changing day in France and for the time between 2 and 3 AM PostgreSQL looks like to change strings to local time and not UTC as for the other dates !!

select '2003-03-30 02:01:47'::timestamp

2003-03-30 03:01:47

Any explanation and How to get the timestamp as expected (without changing server configuration) ?

CodePudding user response：

In the case of timestamp without time zone, I cannot explain your behavior because this does not happen on my side.

In the case of timestamp with time zone, the story is more complex : between 02:00:00 and 02:59:59, the system converts the string into timestamp while increasing the offset by 1 because of the switch from winter time to summer time and increasing the time by 1 hour because 02:00:00 01 (winter time) = 03:00:00 02 (summer time).

The issue is that at 03:00:00 the conversion doesn't increase the time by 1 anymore in order to implement the fact that we lost 1 hour of time due to the switch.

As a result, the string '03:00:00' is converted into the timestamp '03:00:00 02' which is 59 minutes 59 seconds before the timestamp '03:59:59 02' converted from the string '02:59:59'. The conversions of both strings '03:00:00' and '02:59:59" are inconsistent from a timeline perspective.

I raised a bug on that point suggesting to forbid the conversion of strings between '2003-03-30 02:00:00' and '2003-03-30 02:59:59' with an explicit message saying that the corresponding interval of time has never existed due to the winter time / summer time switch.