Redshift – Error when converting UTC time to local time in where clause

I have some sales data that is recorded in UTC. I am trying to convert it to the local timezone where the sales happened.

I have built up a query as below but get an error saying invalid operation: function to_char(timestamp without time zone, charcter varying, unknown”) does not exist.

select fs.sale_id,fs.store_type,fs.sale_time ,
case when fs.timezone = 'BST' then dateadd( h, 1, fs.sale_time ) when fs.timezone = 'EDT' then dateadd( h,- 4, fs.sale_time ) when fs.timezone = 'CEST' then dateadd( h, 2, fs.sale_time ) when fs.timezone = 'EEST' then dateadd( h, 3, fs.sale_time ) when fs.timezone = 'MSK' then dateadd( h, 3, fs.sale_time ) when fs.timezone = 'WEST' then dateadd( h, 1, fs.sale_time ) else null end, fs.timezone as new_time
from sales fs
where to_char((case when fs.timezone = 'BST' then dateadd( h, 1, fs.sale_time ) when fs.timezone = 'EDT' then dateadd( h,- 4, fs.sale_time ) when fs.timezone = 'CEST' then dateadd( h, 2, fs.sale_time ) when fs.timezone = 'EEST' then dateadd( h, 3, fs.sale_time ) when fs.timezone = 'MSK' then dateadd( h, 3, fs.sale_time ) when fs.timezone = 'WEST' then dateadd( h, 1, fs.sale_time ) else null end, fs.timezone),'yyyy-mm-dd') = '2018-09-01'

Could anyone advice how I could modify this query. I am using redshift DB. Thanks.

Answer

I would suggest to use ‘CONVERT_TIMEZONE’ function, details are listed at following.

https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html

For example, your simple query to convert sales from UTC to EST would be something like below.

select listtime, convert_timezone('PST', listtime) from listing where listid = 16;

It will return something like below.

 listtime       |   convert_timezone
 --------------------+-------------------
2008-08-24 09:36:12     2008-08-24 01:36:12

Advertisement

Answer