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

Question

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 1

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 

Answer 2

Try below: you've syntax error

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,
'yyyy-mm-dd') = '2018-09-01'

Answer 3

Please try this

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),'yyyy-mm-dd') = '2018-09-01'

I think copy paste issue where you had extra- ", fs.timezone".

And I also think it is bad query. Going to kill if table is big with such a big function in where clause.