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?

Answer 1

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