CODEWITHSUNDEEP
postgresqldatetimeamazon-redshift
ErRoR
ErRoR

Reputation: 503

Convert Time Zone in Amazon Redshift

I have a datetime column in a redshift table. It contains date in UTC format. So, when I am querying based on time_zone like UTC+5:30/ UTC+4:30, I want to convert the table datetime column to chosen time_zone and proceed further. It gives me wrong result.

Using this method : CONVERT_TIMEZONE ( ['source_zone',] 'target_zone', 'timestamp')

Query Type 1: Wrong input, correct answer

SELECT id, convert_timezone('UTC+5:30','UTC', date) as converted_time, ingest_date
FROM table_name
WHERE conditions

Query Type 2: Correct input, wrong answer -> It again subtracting 5:30 from the date in column 

SELECT id , convert_timezone('UTC','UTC+5:30',ingest_date) as converted_time, ingest_date
FROM table_name
WHERE conditions

Query Type 3: Wrong input, correct answer

SELECT id,  convert_timezone('UTC','UTC-5:30',ingest_date) as converted_time, ingest_date
FROM table_name
WHERE conditions

How to convert / parse the UTC column into selected timezone?

Upvotes: 2

Views: 10461

Answers (1)

ErRoR
ErRoR

Reputation: 503

In Redshift, CONVERT_TIMEZONE interprets the offset as the time from UTC. For example, an offset of +2 is equivalent to UTC–2, and an offset of -2 is equivalent to UTC+2. CONVERT_TIMEZONE does not use the prefix string when calculating the offset, even if the string represents a valid time zone. For example, 'NEWZONE+2’, 'PDT+2', and'GMT+2' all have the same result. If a string does not include an offset, then it must represent a valid time zone or CONVERT_TIMEZONE returns an error.

For converting time_zone, if you send "UTC+5:30/UTC-4:30", amazon interpreting it as "UTC-5:30 / UTC+4:30".

Now you can convert + into - and vice versa before sending it to redshift.

(http://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html)

Upvotes: 3

Related Questions