Time Zones and DST
First of all I am bringing in my data through workbench and using the Shift Date Timezone Transform to convert my data from EST to UTC. Our company settings are setup for EST. All of my dataflows have been using the convert_tz() function on the datetime fields and everything was working until Daylight Savings Time began. You will see from the data below that my 2018-03-12 is off 1 hour and reporting the start_date as 2018-03-11. Hopefully somebody can help me sort out what I need to do to correct the issue. At the end of the day I need to determine if a record is valid on a specific date and I do this by left joining a date table where the date is between the start_date and end_date of the record. It appears to me that this join using the convert_tz() function is not giving me the intended result since DST began. I had a similar issue on another dataset once DST started so I added a date field to avoid the timezone conversion issue since the times were not relevant to that dataset but it seems like a work around and not a solution. Thank you in advance for your assistance.
Raw data
trans_no start_date
315877127001 2018-03-10 00:00:00.000
402385081001 2018-03-12 00:00:00.000
Workbench data with shift date time zone transform
trans_no start_date
315877127001 2018-03-10T05:00:00
402385081001 2018-03-12T04:00:00
mySQL dataflow result
trans_no start_date convert_tz(start_date,'utc','est')
315877127001 | 3/10/2018 5:00:00 AM | 3/10/2018 12:00:00 AM |
402385081001 | 3/12/2018 4:00:00 AM | 3/11/2018 11:00:00 PM |
-----------------
Chris
Comments
-
In order to support Daylight Savings Time in a dataflow the full time zone name must be used in the convert time zone function.
mySQL - select convert_tz(Now(),'utc','us/eastern')
Redshift - select convert_timezone('utc','america/new_york',GetDate())
-----------------
Chris3
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive