Converting timestamp to timestamp in a specific time zone in Postgres

Question

I have a table that has a column whose data type is timestamp without time zone , I'm trying to convert it to timestamp without time zone at a given time zone.

These are my Postgres 9.3 settings

select current_setting('TIMEZONE');
 current_setting
-----------------
 Hongkong

select * from  pg_timezone_names where name = 'Hongkong';
   name   | abbrev | utc_offset | is_dst
----------+--------+------------+--------
 Hongkong | HKT    | 08:00:00   | f

Here is what I did to convert it to HKT:

-- this SQL gives me what I expected
select '2015-01-05 12:00:00'::timestamp without time zone
at time zone 'UTC'
at time zone 'HKT';
---------------------
 2015-01-05 20:00:00

-- Shouldn't this produce the same result with the above one?
-- How do I make this work?
-- Don't tell me to change it to UTC-08:00 ...
select '2015-01-05 12:00:00'::timestamp without time zone
at time zone 'UTC'
at time zone 'UTC+08:00';
---------------------
 2015-01-05 04:00:00 -- WHY?

Use the AT TIME ZONE operator.

Craig Ringer
– Craig Ringer

2015-01-06 04:57:58 +00:00
Commented Jan 6, 2015 at 4:57 — Craig Ringer
– Craig Ringer, Commented Jan 6, 2015 at 4:57

Community · Accepted Answer · 2020-06-20 09:12:55Z

The reason behind this is why you really shouldn't use time-zone offsets in PostgreSQL (unless you are know exactly what you do).

The time zone 'UTC+08:00' is a POSIX-style time zone specification, 'Hongkong' is the exact time zone name, and 'HKT' is (one of) its abbreviation(s).

The pg_timezone_names system view's utc_offset column is defined to be the Offset from UTC (positive means east of Greenwich).

But in the POSIX-style time zone specification, the offset part is different:

... Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

So, instead of using offsets (as intervals), or POSIX-style time zone specifications, you should use:

Time zone abbreviations, if you want to deal with daylight saving rules on your own,
Exact time zone names, everywhere else (preferred).

In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets.

To complicate matters, some jurisdictions have used the same timezone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST example above, this is not necessarily the same as local civil time on that date.

But the most simple solution is to use timestamp with time zone: you already set your TimeZone setting to 'Hongkong', so timestamp with time zone values will be displayed in that time zone to your (PostgreSQL) client.

set time zone 'Hongkong';

select current_setting('TimeZone') TimeZone,
       dt original,
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC+08:00' "UTC+08:00",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC+8' "UTC+8",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC-8' "UTC-8",
       dt AT TIME ZONE 'UTC' AT TIME ZONE INTERVAL '+08:00' "INTERVAL +08:00",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'HKT' "HKT",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'Hongkong' "Hongkong",
       dt AT TIME ZONE 'UTC' "with time zone"
from (values (timestamp '2015-01-05 12:00:00')) v(dt);

-- TIMEZONE | ORIGINAL            | UTC+08:00
-- ---------+---------------------+--------------------
-- Hongkong | 2015-01-05 12:00:00 | 2015-01-05 04:00:00

-- UTC+8               | UTC-8               | INTERVAL +08:00
-- --------------------+---------------------+--------------------
-- 2015-01-05 04:00:00 | 2015-01-05 20:00:00 | 2015-01-05 20:00:00

-- HKT                 | HONGKONG            | WITH TIME ZONE
-- --------------------+---------------------+-----------------------
-- 2015-01-05 20:00:00 | 2015-01-05 20:00:00 | 2015-01-05 20:00:00+08

SQLFiddle

Collectives™ on Stack Overflow

Converting timestamp to timestamp in a specific time zone in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related