Buy Percona ServicesBuy Now!

Things you didn’t know about MySQL and Date and Time and DST

 | April 6, 2021 |  Posted In: MySQL, Open Source Databases, Tools

(based on a conversation with a colleague, and a bit of Twitter)

A Conundrum

A developer colleague paged me with this:

It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:

It has to be exactly 730 days (2 * 365 days, 2 years):

The Reason

In our math, we have two expressions mixing MySQL Timestamp data types with UNIX Timestamp Integers.

So in the expression UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 year) the part "2021-03-26 03:07:00" is a string, which is converted to a MySQL Timestamp type.

This MySQL Timestamp type is then used in an interval arithmethic expression to yield another MySQL Timestamp type.

This resulting MySQL Timestamp type is then fed into the UNIX_TIMESTAMP function, and produces an integer.

The same happens with UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 2 year), producing another integer.

This is not the integer we are looking for:

 

The First Level of Wrongness

The 2023-03-26 is the day of the proposed time zone switch for 2023.

On this date, in the CET/CEST time zone, 02:07:00 is an invalid timestamp. MySQL silently, without error or warning, rounds this up to the next valid timestamp, 03:00:00.

This also happened yesterday:

This should error, and must at least warn. It does neither.

The Second Level of Wrongness

For

there is the choice of producing the correct timestamp or producing an error. Silently fast forwarding to the next valid timestamp is incorrect in all cases.

Setting UTC

The database is running with the time_zone set to SYSTEM, and the system is running with the system_time_zone (a read-only variable) set to CEST (was: CET), which was picked up after the server start (on my laptop, in this case).

Trying to set the time_zone to UTC fails. This is because the time_zone tables have not been loaded.

With that, I can

And with that, I can avoid the conversion:

This will also yield the correct result for the type-mixed difference I showed above:

 

Not mixing MySQL Date Types and UNIX Timestamps

The original math fails, because it mixes UNIX Timestamps and Date Interval Arithmethics.

We can handle this all the way in MySQL, using the extremely weird timestampdiff() function (more on that below):

We can handle this all the way in Integers with Unix Timestamps:

Both give us correct results.

Date and Time Syntax

MySQL provides you INTERVAL syntax with operators:

and with functions:

Interval Syntax is weird. You can’t

You can only

With date_add() it is worse, because you have to nest:

That would be then

So date_add() and date_sub() both take a timestamp and an interval, and can be written as + and -, avoiding the nesting.

A Word of Warning on DIFF functions

There are two functions with underscores in the name, DATE_ADD() and DATE_SUB(), which take a timestamp and an interval. They produce a new timestamp.

There are three functions without underscores in the name DATEDIFF(), TIMEDIFF() and TIMESTAMPDIFF(), which take two timestamps and produce the difference.

They are all subtly different, and the parameter order for TIMESTAMPDIFF() is the other way around.

Read carefully:

datediff(a, b) calculates the DATE difference as a-b. The time part of the timestamps is ignored.

timediff(a, b) calculates the TIME difference as a-b. The DATE and TIME parts are being used. The range is limited to the range of the TIME type, which is from ‘-838:59:59’ to ‘838:59:59’.

That is 5 weeks, less 1 hour and 1 second (5 weeks are 840 hours, 5 * 7 * 24).

timestampdiff(unit, a, b) can do “proper” difference between b and a. The result is reported in the unit specified.

The order of the parameters is inexplicably reversed: We calculate b-a.

TL;DR

  • The lack of warning and error is now a MySQL Service Request.
  • The original problem comes up because of the mixing of Unix Timestamp Arithmethic and MySQL Interval Arithmethic.
  • There are ways to do it pure play either way, and they both result in the right result.
  • There is DATEDIFF(), TIMEDIFF(), and TIMESTAMPDIFF(), and they are weird, and inconsistent and you really, really want to read the Date and Time Functions page, very carefully.

First published on https://blog.koehntopp.info/ and syndicated here with permission of the author.

Kristian Köhntopp

Senior Scalability Engineer at Booking.com. Twitter, LinkedIn, GitHub