CODEWITHSUNDEEP
mysqltimezonetimezone-offset
Andrew Brēza
Andrew Brēza

Reputation: 8317

Convert Eastern timezone to UTC

I'm working with a system that records timestamps in local time. I'm trying to convert to UTC. The time offset changes based on whether or not it's currently daylight savings time. Right now I'm using the code below to manually check if a given event_ts is in daylight savings time, but it seems like there has to be a better way.

SELECT
  IF(DATE(event_ts) BETWEEN '2020-03-08' AND '2020-11-01'
    OR DATE(event_ts) BETWEEN '2021-03-14' AND '2021-11-07'
    OR DATE(event_ts) BETWEEN '2022-03-13' AND '2022-11-06'
    OR DATE(event_ts) BETWEEN '2023-03-12' AND '2023-11-05'
    OR DATE(event_ts) BETWEEN '2024-03-10' AND '2024-11-03'
    OR DATE(event_ts) BETWEEN '2025-03-09' AND '2025-11-02'
    OR DATE(event_ts) BETWEEN '2026-03-08' AND '2026-11-01'
    OR DATE(event_ts) BETWEEN '2027-03-14' AND '2027-11-07'
    OR DATE(event_ts) BETWEEN '2028-03-12' AND '2028-11-05'
    OR DATE(event_ts) BETWEEN '2029-03-11' AND '2029-11-04',
        CONVERT_TZ(event_ts, '-04:00', '+00:00'),
        CONVERT_TZ(event_ts, '-05:00', '+00:00')) AS 'Timestamp'
FROM events

Upvotes: 0

Views: 90

Answers (1)

ysth
ysth

Reputation: 98398

First, there's a utility program to load a system's IANA timezone data (tzdata) into mysql:

https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html

Once you've run that, you can use IANA timezones in CONVERT_TZ:

CONVERT_TZ(event_ts, 'America/New_York', '+00:00') As 'Timestamp'

Rerun it if you update your system tzdata files (which can happen a number of times a year).

Upvotes: 1

Related Questions