MySQL Datetime Fields and Daylight Savings Time - How to Reference the "Extra" Hour

MySQL datetime fields and daylight savings time -- how do I reference the extra hour?

MySQL's date types are, frankly, broken and cannot store all times correctly unless your system is set to a constant offset timezone, like UTC or GMT-5. (I'm using MySQL 5.0.45)

This is because you can't store any time during the hour before Daylight Saving Time ends. No matter how you input dates, every date function will treat these times as if they are during the hour after the switch.

My system's timezone is America/New_York. Let's try storing 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100).

Here's using the proprietary INTERVAL syntax:

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

Even FROM_UNIXTIME() won't return the accurate time.

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

Oddly enough, DATETIME will still store and return (in string form only!) times within the "lost" hour when DST starts (e.g. 2009-03-08 02:59:59). But using these dates in any MySQL function is risky:

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

The takeaway: If you need to store and retrieve every time in the year, you have a few undesirable options:

1. Set system timezone to GMT + some constant offset. E.g. UTC

2. Store dates as INTs (as Aaron discovered, TIMESTAMP isn't even reliable)

3. Pretend the DATETIME type has some constant offset timezone. E.g. If you're in America/New_York, convert your date to GMT-5 outside of MySQL, then store as a DATETIME (this turns out to be essential: see Aaron's answer). Then you must take great care using MySQL's date/time functions, because some assume your values are of the system timezone, others (esp. time arithmetic functions) are "timezone agnostic" (they may behave as if the times are UTC).

Aaron and I suspect that auto-generating TIMESTAMP columns are also broken. Both 2009-11-01 01:30 -0400 and 2009-11-01 01:30 -0500 will be stored as the ambiguous 2009-11-01 01:30.

Datetime behind an hour after insertion. Daylight savings

Not sure why this was happening, but I fixed the problem by ditching Java.Sql.Timestamp in favour of Java.Time.LocalDateTime.

My insertion code now looks like below (where localDateTime is of type LocalDateTime rather than Timestamp):

jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement stmt = con.prepareStatement(
                "INSERT INTO Table (date) VALUES (?)");
        stmt.setObject(5,localDateTime));
        return stmt;
    }
});

The MySql database no longer automatically adjusts for timezone.

MySQL query to group results by hour of day, taking into account daylight savings

CONVERT_TZ:

SELECT 
    HOUR(CONVERT_TZ(`somedatetime`, 'UTC', 'Europe/Amsterdam')) AS `hour`, 
    COUNT(*) AS `count`
FROM table
GROUP BY `hour`

Pass the user's timezone into the query in place of 'Europe/Amsterdam'.

See also: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html (You may need to intialise MySQL with timezone data.)

Configure mySQL to inherit the operating system's timezone

The answer seems to be to add a hash before default-time-zone from /etc/my.cnf to disable it, then restart MySQL.

As the manual describes, MySQL will default to system_time_zone if the default is not explicitly set.

If this option is not given, the default time zone is the same as the system time zone (given by the value of the system_time_zone system variable.

MySQL daylight savings info seems to be incorrect. How can I ensure it is up to date in Windows?

The relevant documentation is here:
https://dev.mysql.com/doc/refman/5.5/en/time-zone-upgrades.html

The key sentences are:

"The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM"

and

"If you use named time zones with MySQL, make sure that the time zone tables in the mysql database are up to date."

Later the documentation states that if the mysql.time_zone_name table is empty, then no one can be using named time zones, and you don't need to update the tables.

---

So, to force MySQL to use operating system information for time zones and daylight savings periods, set the timezone to 'SYSTEM'.

Also, if the timezone is set to 'SYSTEM' you do not need to fill the mysql.time_zone tables.

---

After setting the MySQL time zone to 'SYSTEM' by running 'SET time_zone="SYSTEM"', I re-ran the script from the question and got the following output, which shows PHP and MySQL behaving the same way as expected.

date_default_timezone_get(): Australia/Melbourne
date('P'): +10:00
MySQL timezone: AUS Eastern Standard Time
 * timestring        : 2018-10-07 00:00:00
   timestampFromMySql: 1538834400
   timestampFromPhp  : 1538834400
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 01:00:00
   timestampFromMySql: 1538838000
   timestampFromPhp  : 1538838000
   date('I', $timestampFromMySql): 0
   date('I', $timestampFromPhp  ): 0
   difference: 0
 * timestring        : 2018-10-07 02:00:00
   timestampFromMySql: 1538841600
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0
 * timestring        : 2018-10-07 03:00:00
   timestampFromMySql: 1538841600
   timestampFromPhp  : 1538841600
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0
 * timestring        : 2018-10-07 04:00:00
   timestampFromMySql: 1538845200
   timestampFromPhp  : 1538845200
   date('I', $timestampFromMySql): 1
   date('I', $timestampFromPhp  ): 1
   difference: 0

php + mysql and daylight saving time issues: what are the rules?

If you ask me, a great way to get around it is to store unix timestamps rather then the actual date string for this very reason. Leave it to your application to convert to the local timezone instead. Otherwise clock switches can always be a pain. Another advantage to this is any ordering or selecting you may need to do on the table is much more efficient on integers rather then date strings.

This may be a little more time consuming then you are hoping for, but if you store the local time in the local time format in your database, you will continue to run into these kinds of problems.

How to store a datetime in MySQL with timezone info

You said:

I want them to always come out as Tanzanian time and not in the local times that various collaborator are in.

If this is the case, then you should not use UTC. All you need to do is to use a DATETIME type in MySQL instead of a TIMESTAMP type.

From the MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

If you are already using a DATETIME type, then you must be not setting it by the local time to begin with. You'll need to focus less on the database, and more on your application code - which you didn't show here. The problem, and the solution, will vary drastically depending on language, so be sure to tag the question with the appropriate language of your application code.

---

Related Topics