Convert_Timezone() function
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
-----------------
Chris
Best Answer
-
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.
-----------------
Chris0
Answers
-
Hi,
Is anyone able to help out with this request?
Thanks,0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 465 Domo Developer
- 50 Domo Everywhere
- 106 Apps
- 717 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 27 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 654 ひらめき共有