CODEWITHSUNDEEP
sqlcaseamazon-redshift
Kevin Nash
Kevin Nash

Reputation: 1561

Redshift - Converting UTC data to other timezones

I am trying to convert data from UTC to various European timezones. I am using a case statement and find only the first condition in the case statement is executed while the other conditions are not checked. 

SELECT sale_id,appointment_time,timezone,
   case when timezone = 'EDT' then (appointment_time + interval '-4' HOUR * 1)
        when timezone = 'BST' then (appointment_time + interval '1' HOUR * 1)
        when timezone = 'CEST' then (appointment_time + interval '2' HOUR * 1)
        when timezone = 'EEST' then (appointment_time + interval '3' HOUR * 1)
        when timezone = 'MSK' then (appointment_time + interval '3' HOUR * 1)
        when timezone = 'WEST' then (appointment_time + interval '1' HOUR * 1) 
        else null
        end as NewTime
   FROM sales

Could anyone advice as to where am I going wrong. Thanks

Upvotes: 0

Views: 531

Answers (2)

demircioglu
demircioglu

Reputation: 3465

Why don't you use built in convert_timezone function. It would be faster since you don't need to use a case

SELECT sale_id, appointment_time, timezone, 
  convert_timezone(timezone, appointment_time) as NewTime
FROM sales

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

you missed else just add before end and use dateadd function

SELECT sale_id,appointment_time,timezone,
   case when timezone = 'EDT' then dateadd(h,-4,appointment_time)
        when timezone = 'BST' then dateadd(h,1,appointment_time)
        --------------
         --------------
       else null
        end as NewTime
   FROM sales

Upvotes: 1

Related Questions