0

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.

0
SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET' - '2017-02-08 15:43:27.151' AT TIME ZONE 'UTC'
|improve this answer|||||
  • 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'; – Mio Feb 9 '17 at 9:05
  • 1
    Question is about +01:00 as answer. Result of your query is -01:00 – Roman Tkachuk Feb 9 '17 at 9:07
  • 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' – Mio Feb 9 '17 at 9:55
  • CET - is Central European Time. It is + 1 hour to UTC. – Roman Tkachuk Feb 9 '17 at 10:20
0

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();
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.