Search code examples
datetimetimezoneamazon-redshiftdstdate-conversion

Taking care of daylight saving time when converting timezone

I have a Redshift data table where all time values are stored in CST and I convert the time values to the respective timezone based on the zip code (location).

While I do that, I understand that all time values are in Standard time and hence my function usage is 

CASE WHEN **** convert_timezone('CST', 'EST', time_column)  
     WHEN **** convert_timezone('CST', 'MST', time_column)
....
END

This may not be applicable once we enter into Daylight Savings time. How can I take care of this such that I do not modify the SQL query again in 2018 March and in future?

Solution

  • Don't use time zone abbreviations. The are somewhat ambiguous, and can only refer to one aspect of the time zone. Instead, use a full IANA time zone identifier, such as America/Chicago for US Central time.

    This is explained well in the Redshift docs:

    Using a Time Zone Name

    If you specify a time zone using a time zone name, CONVERT_TIMEZONE automatically adjusts for Daylight Saving Time (DST), or any other local seasonal protocol, such as Summer Time, Standard Time, or Winter Time, that is in force for that time zone during the date and time specified by 'timestamp'. For example, 'Europe/London' represents UTC in the winter and UTC+1 in the summer.

    As far as the "...based on the zip code" part of your question, understand that not every ZIP code is locality-based. There are also technical assignments, overseas APO/FPO addresses, US territories, and other edge cases. Additionally, some zip codes may straddle more than one time zone.

    A better approach, when possible, is to:

    • Get an approximation of latitude/longitude coordinates - using a variety of techniques depending on your source data. For example, geocoding APIs can take a street address and give a lat/lon.
    • Then determine the time zone identifier for that location, using one of the techniques listed here.