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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive