Time Zones and DST

cwolman

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

 

 

cwolman

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())