Postgresql: convert UTC to CET preserving time zone in the resulting value

Question

I am trying to convert UTC to CET and preserve the timezone in the resulting value in postgresql.

Example: Initial value: '2021-01-16 23:00:23+00:00' (is stored as text or as timestamp with timezone) Desired output: 2021-01-17 00:00:23+01:00 (stored as timestamp with timezone)

I tried the following (when the value is stored as text):

select TO_TIMESTAMP(('2021-01-16 23:00:23+00:00' at time zone 'cet' || '+01:00'), 'YYYY/MM/DD HH24:MI:SS')

And for value stored at timestamp with time zone:

select TO_TIMESTAMP((('2021-01-16 23:00:23+00:00'::TIMESTAMP WITH TIME ZONE) at time zone 'cet' || '+01:00'), 'YYYY/MM/DD HH24:MI:SS')

The output is 2021-01-17 00:00:23+00:00, which is not a desired output, because the time zone should be +01:00 and not 00:00.

How do I get the desired output?

EDIT:

I figured it out. My timezone setting in postgresql was "GMT". If I set it to "CET". set timezone TO 'CET'; then the following works:

select '2021-01-16 23:00:23+00:00'::TIMESTAMP WITH TIME ZONE;

outputs: 2021-01-17 00:00:23+01

`select '2021-01-16 23:00:23+00:00'::timestamp at time zone 'cet';`? — Abelisto, Jan 17 '21 at 13:53
@Abelisto this give me completely wrong result: 2021-01-16 22:00:23+00 — Alina, Jan 17 '21 at 13:56
What value does your local timezone have? (`SELECT * FROM pg_timezone_names WHERE name = current_setting('TIMEZONE');`) [1](https://stackoverflow.com/questions/28216923/how-do-i-get-the-current-timezone-name-in-postgres-9-3) — Luuk, Jan 17 '21 at 13:58
The output of `timestamptz` value converted to the your local time zone and contains it. If you want the output of the `text` type then `select format('%s%s', '2021-01-16 23:00:23+00:00'::timestamptz at time zone 'CET', '+01:00');` You can to use the `pg_timezone_abbrevs` table to get the actual offset for any time zone by its abbrevation. — Abelisto, Jan 17 '21 at 14:08

score 2 · Accepted Answer · answered Jan 17 '21 at 19:36

In PostgreSQL timezones are just a display/parsing mechanism. Internally, both timestamp and timestamptz are stored in UTC and neither contains any timezone information. See this answer for an in-depth explanation.

In other words: you cannot store a timestamp with a specific time zone. You could store a desired time zone in a separate column, or store the timestamp & timezone combination as a text (containing some timezone abbreviation or name).

Practically, I like to keep a couple of functions around, for ease of timezone and Epoch conversions:

-- convert seconds since the epoch (like a unix timestamp) into a timestamp
-- at a specified timezone (default: local timezone).
create or replace function unix2ts(seconds_since_epoch float, tz text)
returns timestamp as $$
select (timestamp with time zone 'epoch' + $1 * interval '1 second') at time zone $2;
$$ language sql;
create or replace function unix2ts(seconds_since_epoch float)
returns timestamp as $$
select (timestamp with time zone 'epoch' + $1 * interval '1 second')::timestamp;
$$ language sql;


-- convert timestamp or timestamptz to seconds since the epoch.
create or replace function ts2unix(t timestamp)
returns double precision as $$
select extract (epoch from $1);
$$ language sql;
create or replace function ts2unix(t timestamptz)
returns double precision as $$
select extract (epoch from $1);
$$ language sql;

Parsing example

show timezone;

select txt, seconds, unix2ts(seconds, 'utc') as utctime, unix2ts(seconds) as localtime
from (
  select txt, extract (epoch from txt::timestamptz) as seconds
  from (
    select a || b as txt
    from
      unnest('{2021-01-01,2021-08-01}'::text[]) as a,
      unnest('{""," Z",:PST,:PDT,:PST8PDT,:CET,+01:00}'::text[]) as b
  ) as c
) as d;

Output:

    TimeZone    
----------------
 Canada/Pacific

        txt         |  seconds   |       utctime       |      localtime      
--------------------+------------+---------------------+---------------------
 2021-01-01         | 1609488000 | 2021-01-01 08:00:00 | 2021-01-01 00:00:00
 2021-01-01 Z       | 1609459200 | 2021-01-01 00:00:00 | 2020-12-31 16:00:00
 2021-01-01:PST     | 1609488000 | 2021-01-01 08:00:00 | 2021-01-01 00:00:00
 2021-01-01:PDT     | 1609484400 | 2021-01-01 07:00:00 | 2020-12-31 23:00:00
 2021-01-01:PST8PDT | 1609488000 | 2021-01-01 08:00:00 | 2021-01-01 00:00:00
 2021-01-01:CET     | 1609455600 | 2020-12-31 23:00:00 | 2020-12-31 15:00:00
 2021-01-01+01:00   | 1609455600 | 2020-12-31 23:00:00 | 2020-12-31 15:00:00
 2021-08-01         | 1627801200 | 2021-08-01 07:00:00 | 2021-08-01 00:00:00
 2021-08-01 Z       | 1627776000 | 2021-08-01 00:00:00 | 2021-07-31 17:00:00
 2021-08-01:PST     | 1627804800 | 2021-08-01 08:00:00 | 2021-08-01 01:00:00
 2021-08-01:PDT     | 1627801200 | 2021-08-01 07:00:00 | 2021-08-01 00:00:00
 2021-08-01:PST8PDT | 1627801200 | 2021-08-01 07:00:00 | 2021-08-01 00:00:00
 2021-08-01:CET     | 1627772400 | 2021-07-31 23:00:00 | 2021-07-31 16:00:00
 2021-08-01+01:00   | 1627772400 | 2021-07-31 23:00:00 | 2021-07-31 16:00:00

Display example

select seconds, unix2ts(seconds, 'utc') as utctime,
  unix2ts(seconds, atzone) || ' ' || atzone as atzone
from unnest('{1609459200,1627776000}'::int[]) as seconds,
unnest('{PST,PDT,PST8PDT,America/Los_Angeles,CET,Europe/Paris}'::text[]) as atzone;

Output:

  seconds   |       utctime       |                 atzone                  
------------+---------------------+-----------------------------------------
 1609459200 | 2021-01-01 00:00:00 | 2020-12-31 16:00:00 PST
 1609459200 | 2021-01-01 00:00:00 | 2020-12-31 17:00:00 PDT
 1609459200 | 2021-01-01 00:00:00 | 2020-12-31 16:00:00 PST8PDT
 1609459200 | 2021-01-01 00:00:00 | 2020-12-31 16:00:00 America/Los_Angeles
 1609459200 | 2021-01-01 00:00:00 | 2021-01-01 01:00:00 CET
 1609459200 | 2021-01-01 00:00:00 | 2021-01-01 01:00:00 Europe/Paris
 1627776000 | 2021-08-01 00:00:00 | 2021-07-31 16:00:00 PST
 1627776000 | 2021-08-01 00:00:00 | 2021-07-31 17:00:00 PDT
 1627776000 | 2021-08-01 00:00:00 | 2021-07-31 17:00:00 PST8PDT
 1627776000 | 2021-08-01 00:00:00 | 2021-07-31 17:00:00 America/Los_Angeles
 1627776000 | 2021-08-01 00:00:00 | 2021-08-01 01:00:00 CET
 1627776000 | 2021-08-01 00:00:00 | 2021-08-01 02:00:00 Europe/Paris

A few things to note:

3-letter timezone abbreviations (e.g. 'PST' or 'PDT') typically define a fixed time offset and won't magically adjust to -8, resp. -7, depending on daylight savings. To achieve that, use either 'PST8PDT' or (better IMHO) the geographic timezone 'America/Los_Angeles' or other geographic ones defined in pg_timezone_names.
When parsing from text, timestamp ignores any timezone information you could have added in the text (e.g.: '1970-01-01 00:00:00 Z') and instead interprets the timestamp in the timezone currently in effect; it will also display the timestamp in that same local timezone, so you really have no idea until you look at the seconds from the Epoch, or you change the local timezone. IMHO, it is sneaky and confusing (but SQL standard). For example: select ts2unix('1970-01-01 Z'::timestamp) with a current local timezone as 'Canada/Pacific' gives 28800!
Timezones are kind of evil. Some times just don't exist (e.g. '2021-03-14 02:30:00 PST8PDT' parses to the same instant as '2021-03-14 03:30:00 PST8PDT'). During daylight savings to standard, two different instants could lead to the same displayed time (e.g. 2020-11-01 01:30:00 PST8PDT for both 08:30 Z and 09:30 Z). Some places have timezones that are offset by non-integer number of hours (e.g. IST: 05:30:00 or CHADT: 13:45:00).

Postgresql: convert UTC to CET preserving time zone in the resulting value

1 Answers1

Parsing example

Display example