Redshift – Convert UTC Time to Local Time with Specific Format

redshifttimezone

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.

Best Answer

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