how to ignore DST in MySQL

Question

We have a database with flights, and in there, departureTime DATETIME field.

Extra info:

global.time_zone: Europe/Dublin
session.time_zone: Europe/Dublin
MySQL version: 5.7

Problem is, I need to save a flight from China at "2019-03-31 01:00", and server is saving "2019-03-31 02:00" because of the day saving time. In Dublin would be fine, but that flight is from China and should be saved properly.

So what would be the best here?,

Is there any way to ignore DST validations?.

Should I save all datetimes in UTC format and then show them in each timezone?

Should I use timestamp instead of datetime?

If I change server timezone to UTC, what would it happen with old records?

Answer 1

MySQL does not have a datatype that will receive a local time and timezone. You will need to do one of these:

• Store both fields and worry about interpreting it later.
• Use application code to convert the time to UTC based on the given TZ. Then store it in a TIMESTAMP while having the system time set to UTC.

When you store 2019年03月31日 01:00 into a DATETIME, 2019年03月31日 01:00 is the only thing you will ever get from that column.

When you store something in a TIMESTAMP column, it is converted according to MySQL's tz setting, then stored as UTC. When retrieving, it will be reconverted. If nothing has changed, you will get the same value back.

Between 2am and 3am is tricky because that hour repeats once a year. Beware.

If the flight leaves China at 2019年03月31日 01:00 China time, but your system is set for Dublin time, neither DATETIME, nor TIMESTAMP will be useful.

Best to convert to/from UTC in your app, then store UTC in the database.