Search code examples
postgresqlutcdst

Converting DateTime field without timezone in Postgres from Middle European TIme to UTC

I wanted to convert (UPDATE in Postgres with SQL) a DateTime field without timezone from Middle European Time to UTC. There is a problem here that the DateTime may be daylight time or winter time so that the DateTime is UTC+1 or UTC+2. The sticking point is to find the the DST boundary with Postgres with bord means.

I tried to write a stored procedure that could find the above mentioned DST boundary but I didn't find any simply suitable solution but this one: https://www.keithf4.com/postgresql_dst/

Is there maybe a simpler solution?

Solution

  • That is simple. Convert it to an absolute timestamp and back. If you specify the time zone correctly, daylight savings time will be considered automatically.

    For Austria, it would look like this:

    SELECT TIMESTAMP '...' AT TIME ZONE 'Vienna/Europe' AT TIME ZONE 'UTC';