Summer and wintertime postgres timezone/timestamp

Question

I uploaded csv data into table in postgres. One of the columns is a character varying column with an epoch filling. The data going in is as following:

Example record: 1507656308.

The epoch character varying column is transformed to a timestamp column as follows:

select 
    TIMESTAMP WITH TIME ZONE 'epoch' + timestamp::integer * INTERVAL '1 second' as timestamp
from
    table

The result appeared good and I continued with it. I live in the Netherlands and here we have the winter-summertime change twice a year. Apparently my data does not differentiate.

I tried to figure out my current timezone. Which I did like this:

SELECT EXTRACT(TIMEZONE FROM timestamp) FROM table;

Resulting in:

3,600.0000

When I try to figure out my general time zone I get this:

show timezone
= Europe/Berlin

How do I get my timestamps to recognize the summer vs wintertime? Or what did I do wrong?

to support time zones/DST you should use timestamptz data type insetad — Vao Tsun

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-05-15T18:51:29

Of all the creative ways to convert an epoch value (why is it stored as string?) to a timestamp with time zone, this is one of the more interesting one.

The simple and boring way is

SET timezone = 'Europe/Berlin';

SELECT to_timestamp(1507656308);

      to_timestamp      
------------------------
 2017-10-10 19:25:08+02
(1 row)

The time zone offset for that is two hours, because on October 10, daylight savings time was in effect (Central European Summer Time).

Indeed:

SELECT extract(timezone FROM to_timestamp(1507656308));

 date_part 
-----------
      7200
(1 row)

So everything is working as expected, right?

Summer and wintertime postgres timezone/timestamp

1 Answers