Convert UTC time to local time with specific format in Redshift

Question

I have a column called created_at with data stored in UTC without timezone like : 2017年02月05日 15:43:27.151

I would like to convert to an other timezone like CET in specific format like :

2017年02月08日 16:43:27 +01:00

I succeed converting the time to specific timezone but needed to specify the timezone of the input. Query looks like :

SELECT created_at, 
 (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'CET' AS cet 
FROM my_table;
[
 {
 "created_at" : "2017-02-08 15:43:27.151",
 "cet" : "2017-02-08 16:43:27.151"
 }
]

But I failed to extract or generate from timezone code the timezone offset.

I tried

SELECT EXTRACT(TIMEZONE_HOUR
 FROM
 (SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET'));
[
 {
 "date_part" : 0
 }
]

How to get the 2017年02月08日 16:43:27 +01:00 ?

EDIT 1:

SHOW timezone; --utc
SELECT current_timestamp; -- 2017年02月09日 11:00:20.225039+00
SELECT to_char(current_timestamp AT TIME ZONE 'CET', 'YYYY-MM-DD HH24:MI:SS OF'); -- 2017年02月09日 12:00:35 +00

Why the offset is 00? (I'm using OF because I'm on Redshift)?

OF | Offset from UTC; valid for TIMESTAMPTZ only

From doc

EDIT 2: I thought AT TIME ZONE 'CET' turn the the date to timestamptz but I was wrong. You need to cast the date as ::timestamptz then OF will display the UTC offset.

SELECT to_char((current_timestamp AT TIME ZONE 'CET')::timestamptz, 'YYYY-MM-DD HH24:MI:SS OF'); 
--2017年02月09日 14:48:48 +01

Now I need to add the :00.

Answer 1

SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET' - '2017-02-08 15:43:27.151' AT TIME ZONE 'UTC'

• Mio: More the opposite no? SELECT '2017年02月08日 15:43:27.151' AT TIME ZONE 'UTC' - '2017年02月08日 15:43:27.151' AT TIME ZONE 'CET';
• Roman Tkachuk: Question is about +01:00 as answer. Result of your query is -01:00
• Mio: Hmm. I have this SHOW timezone; -- 'utc' SELECT '2017年02月08日 15:43:27.151' AT TIME ZONE 'UTC' - '2017年02月08日 15:43:27.151' AT TIME ZONE 'CET'; -- '01:00:00'
• Roman Tkachuk: CET - is Central European Time. It is + 1 hour to UTC.

Answer 2

After searching into the Redshift documentation :

I finally did :

SELECT to_char((current_timestamp AT TIME ZONE 'CET')::timestamptz, 'YYYY-MM-DD HH24:MI:SS OF') || ':00' AS created_at
-- 2017年02月09日 15:10:15 +01:00

Timestamp need to be converted into the expected time zone AT TIME ZONE ... then casted with a time zone otherwise OF will not display the UTC offset.

I add :00 after verifying time zones can't display time zones with minutes :

SELECT pg_timezone_names();