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):
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
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.
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:
Thsi 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.
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).