Convert_Timezone() function

cwolman

This is an optimization issue I have run into when joining tables on dates.  I am left joining a table of dates to the data to create a record for each date that a row is valid for based on the join criteria.  This dataflow used to complete in 15-20 minutes when I was using convert_timezone('utc','est',c.start_date).  DST rolled around and I realized my mistake and began passing the time zone name to the function to account for DST.  My same dataflow now takes 2-3 hours to complete with no other changes.  Below is the sql with the join for reference.  Would anybody have any recommendations to return this dataflow to the previous run times and still account for DST?  Thanks in advance for your help and suggestions.

 

 

select d.datevalue as validon_date, c.resort_id, c.department, c.category, c.item_descrip, c.qty, c.prof_ctr_descrip, c.ledger, c.pr_ctr_category
from raw_pk_dates d
left join combined c ON d.datevalue BETWEEN date(convert_timezone('utc','america/new_york', c.start_date)) and          date(convert_timezone('utc','america/new_york', c.expires))

    and c.admissions=0

 

cwolman

I was able to get the dataflow running in the 15-20 minute range again by converting the time zones before performing the join.

 

Thank you.

laura_igl

Hi,

Is anyone able to help out with this request?

Thanks,

cwolman

I was able to get the dataflow running in the 15-20 minute range again by converting the time zones before performing the join.

 

Thank you.