Insert UTC timestamp into MySQL - Daylight Savings timezone

0
votes

I have a Java client that is inserting (through a CSV and 'LOAD DATA INFILE') timestamps (type TIMESTAMP) in UTC format in a MySQL DB. My server's timezone is "America/New_York" and I need to keep daylight savings information. November 5th at 2:00am is when the time changes from EDT to EST and clocks are set back one hour to 1:00AM. So November 5th 1:30 AM, for instance, happens twice; once in EDT and once in EST.

How can I make the server distinguish between the two? For instance, if I want to insert '2017-11-05 01:30:00' EDT, is there a way to tell MySQL to tell the difference between '2017-11-05 01:30:00' EDT (before the time change) and '2017-11-05 01:30:00' EST (after the time change) when I insert values? My Java application has the timezone information of both timestamps if that matters.

I want to keep the server's global timezone as 'America/New_York' because users access this data and want it displayed in their local time.

The solution I thought of is to set the connection's timezone to UTC from the Java client, insert the values in UTC and then reset the connection's timezone back, but I wanted to know if there was a better, more 'standard' solution.

1
javamysqltimezoneutcdst
If the value is in UTC, there's no DST ambiguity. 2017-11-05T01:30 EDT is the same as 2017-11-05T05:30 UTC and 2017-11-05T01:30:00 EST is 2017-11-05T06:30 UTC. – user7605325
I know. My question is, I have 2 timestamps in Java. One is '2017-11-05 01:30:00' EDT and the other one is '2017-11-05 01:30:00' EST. How can I insert both in the MySQL field of type TIMESTAMP, which is UTC? Do I need to convert them to UTC in Java first and then insert them, or is there a better way? – Vincent L
When you say timestamp, do you mean a java.sql.Timestamp or another type? – user7605325
I have it as a ZonedDateTime at the moment – Vincent L
I know that JDBC 4.2 has support to java.time classes, and I guess most major database vendors already implemented the respective drivers. So, I believe that just converting the ZonedDateTime to Instant (calling toInstant() method) and saving the Instant might work. Or, for JDBC < 4.2, you can convert it to a java.sql.Timestamp using Timestamp.from(zoneddatetime.toInstant()) and save the timestamp (I used mysql a long time ago, but I think that Timestamp and Instant classes are mapped to TIMESTAMP fields - but I would check the docs just to make sure) – user7605325

1 Answers