CODEWITHSUNDEEP
sqltimestampamazon-redshifttimestamp-with-timezone
CodingInCircles
CodingInCircles

Reputation: 2807

How to convert this specific timestamp format to UTC in Redshift?

I have timestamp strings that look something like the example here: 2017-07-12T01:51:12.732-0600. Is there any function/combination of functions I can use this to convert this to UTC accurately?

The output should be 2017-07-12 07:51:12.732000. I've tried using to_timestamp and convert_timezone. Obviously, the latter failed, but so did the former and I'm at my wit's end. Help?

Upvotes: 0

Views: 2292

Answers (1)

AlexYes
AlexYes

Reputation: 4208

you can convert the string directly to timestamp and then set source timezone in convert_timezone function like this (note, offset sign is the opposite to timezone):

select convert_timezone('UTC+06','utc','2017-07-12T01:51:12.732-0600'::timestamp)

if -0600 part is varying you can construct 'UTC+06' part dynamically like this

with times as (
    select '2017-07-12T01:51:12.732-0600'::varchar(28) as ts_col
)
select convert_timezone('utc'+(substring(ts_col from 24 for 3)::integer*(-1))::varchar(3),'utc',ts_col::timestamp)
from times

Upvotes: 2

Related Questions