Add timezone to timestamp in PostgreSQL (not convert to this timezone)

Question

I have 3 separate fields of types VARCHAR (contains timezone, for example 'Europe/Rome'), DATE and INTERVAL. And I want to combine them in query so that I get TIMESTAMP WITH TIME ZONE. The rule of making this value is:

Noon of specified date in given time zone, minus 12 hour and plus given interval (this -12 hours is needed to deal with DST).

Obviously adding/subtracting INTERVAL is not a problem. My problem is that I don't understand how to create "Noon of specified date in given time zone".

A quick example:

If I have timezone field 'Europe/Rome'
Date is 2018-03-24 (Date of DST switch in this timezone)
Interval is '1 hour'

The calculation should be like this:

Create noon of specified date: 2018-03-24 12:00:00 at 'Europe/Rome' time zone
Subtract INTERVAL '12 hours', result is 2018-03-24 01:00:00 at 'Europe/Rome' time zone (because of DST)
Add INTERVAL '1 hour', final result is 2018-03-24 02:00:00 at 'Europe/Rome' time zone.

How do I do point 1?

P.S. I cannot change schema of data. It comes from GTFS data that is loaded into postgres. In short, peculiarity of this schema is that it stores timezone, date and time intervals in 3 different tables: agency, calendar_dates and stop_times (well, timezone may be in other table, but that's not important for this question).

@paulh I need to do this in SQL as this resulting field is part of materialized view — Fly, Jul 31 '18 at 10:09

Laurenz Albe · Accepted Answer · 2018-07-31T14:11:02.797

Your examples are not syntactically correct, but your problem seems to be that PostgreSQL interprets the string literal with the date in it as timestamp with time zone.

But you have to go the other way: you want twelve noon as timestamp without timezone, then use AT TIME ZONE 'Europe/Rome' to get an absolute timestamp (called timestamp with time zone in PostgreSQL) that contains “noon as it is in Rome”, and then add the hour.

The displayed result will depend on your session time zone.

Let's use UTC so that we get the same result and write the above in SQL:

SET timezone=UTC;

SELECT '2018-03-24 12:00:00'::timestamp without time zone
   AT TIME ZONE 'Europe/Rome'
   + INTERVAL '1 hour';

        ?column?        
------------------------
 2018-03-24 12:00:00+00
(1 row)

This requires you to know that Europe/Rome was UTC+1 at that time. — OrangeDog, Jul 02 '20 at 10:42
@OrangeDog The question asked specifically for a given interval. — Laurenz Albe, Jul 02 '20 at 11:11

Add timezone to timestamp in PostgreSQL (not convert to this timezone)

1 Answers1