MySQL Bugs: #95212: MySQL timestamp arithmetics should not produce invalid values

Bug #95212	MySQL timestamp arithmetics should not produce invalid values
Submitted:	1 May 2019 13:12	Modified:	4 May 2019 20:53
Reporter:	Power Gamer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL already performs timestamp corrections when adding and substracting days. For example: '2019-02-28 00:00:00' + INTERVAL 1 DAY will result in a valid '2019-03-01 00:00:00' rather than invalid '2019-02-29 00:00:00' timestamp.

Analogous corrections should be performed for times also. For example, under the effect of "SET time_zone = 'Europe/Kiev';" the calculation '2019-03-31 02:10:00' + INTERVAL 1 HOUR should result in a valid timestamp '2019-03-31 04:10:00' rather than invalid timestamp '2019-03-31 03:10:00' (the hour 03:00 does not exist in Europe/Kiev time zone due to moving clock forward to adjust for DST).

As it stands now, under MySQL "time_zone" set to non-UTC, it is not possible to reliably perform timestamp arithmetic in MySQL and insert the result into some table or have a generated field with timestamp arithmetic in a table and update that table (see examples under "How to repeat"). All these operations will only work reliably under "SET time_zone = UTC" effectively rendering the whole "SET time_zone=some-other-non-UTC-TZ" ability completely useless.

How to repeat:
-- Example 1
CREATE TABLE tbl(a TIMESTAMP NOT NULL);

SET time_zone = 'Europe/Kiev';
INSERT INTO tbl(a) VALUES('2019-02-28 00:00:00');
UPDATE tbl SET a = a + INTERVAL 1 DAY;
SELECT a FROM tbl; -- All OK, date was autocorrected to a valid one: '2019-03-01 00:00:00'

DELETE FROM tbl;

INSERT INTO tbl(a) VALUES('2019-03-31 02:10:00');
UPDATE tbl SET a = a + INTERVAL 1 HOUR; -- Error: Incorrect datetime value: '2019-03-31 03:10:00' - time is NOT auto-corrected for the current timezone!

-- Example 2
CREATE TABLE tbl2(a TIMESTAMP NOT NULL, b TIMESTAMP GENERATED ALWAYS AS (a + INTERVAL 1 HOUR), c INT NOT NULL);
SET time_zone = 'UTC';
INSERT INTO tbl2(a,c) VALUES('2019-03-31 00:10:00', 11);
SET time_zone = 'Europe/Kiev';
UPDATE tbl2 SET c = 22; -- Error: Incorrect datetime value: '2019-03-31 03:10:00' - effectively under a user timezone the table cannot be updated at all !!!

Suggested fix:
Timestamp arithmetics under any supported timezone should NOT produce invalid timestamps (for ANY reason, such as DST time adjustments) as long as the result is within the supported values range of the datatype (which is currently from '1970-01-0100:00:01' UTC to '2038-01-1903:14:07' UTC for TIMESTAMP).

Hi Mr. Gamer,

I have run your test case and I have got the following error message:

ERROR 1292 (22007) at line 7: Incorrect datetime value: '2019-03-31 03:10:00' for column 'a' at row 1

That is how MySQL functions now. It is so by an intentional design. I guess that you would like a new feature that you would change this behaviour. In that case, please let us know exactly what would be the desired behaviour and I will verify your feature request.

The desired behavior (and I think it would be a better behavior than the current one) would be as follows.

Regardless of what time zone is set through "SET time_zone=" the expressions involving +- INTERVAL should result in VALID (for selected time zone) values as long as the original values the expression operated on were valid (for selected time zone) and the resulting value is within the resulting data type range. The following examples will illustrate the rules:

1. SELECT TIMESTAMP '2019-01-33 00:00:00' + INTERVAL 1 DAY;
Throw an error because the operand of expression is invalid (day 33) under current time zone (any time zone in this case) - current behavior.

2. SET time_zone="Europe/Kiev";
SELECT TIMESTAMP '2019-03-31 02:10:00' + INTERVAL 1 HOUR;
The operand of expression ('2019-03-31 02:10:00') is valid under the currently set time zone. So, the result of the expression should also be valid under the current time zone - '2019-03-31 04:10:00'. Current behavior - INVALID date-time '2019-03-31 03:10:00' under the current time zone.

3. SET time_zone="Europe/Kiev";
SELECT TIMESTAMP '2019-01-01 00:00:00' + INTERVAL 9999 YEAR;
The operand of expression ('2019-01-01 00:00:00') is valid under the currently set time zone. But the result of the expression would fall outside the supported date/time range (year is too big). Current behavior - result of the expression is NULL. Keep the current behavior or change to an error (changing to error would prevent inserting erroneous result into a table field accepting NULLs).

3. CREATE TABLE tbl3 (a DATETIME NOT NULL);
SET time_zone="Europe/Kiev";
INSERT INTO tbl3(a) VALUES('2019-03-31 03:10:00');
SELECT a + INTERVAL 1 DAY FROM tbl3;
The operand of expression (value of tbl3.a) is INVALID under currently selected timezone (time '2019-03-31 03:10:00' does not exist in Europe/Kiev), so the expression should result in an ERROR. Current behavior - '2019-04-01 03:10:00' (invalid timestamp + offset results in what kind of result ??).

I think the current behavior of MySQL +- INTERVAL expressions can in short be surmised as: "Regardless of SET time_zone value treat all date-times as if UTC and perform additions and subtractions" (BTW, while being "intentional design" I have not seen it being mentioned anywhere in the documentation). It should be changed to "Honor the value of SET time_zone and treat all date-time values in that time zone when performing additions and subtractions". That new behavior can be implemented as "convert all date-times to UTC from currently set timezone, perform additions and subtractions and convert result back to currently set time zone".

Here the report of another user who basically wanted the same feature in MySQL: https://bugs.mysql.com/bug.php?id=60550.

Hello Mr. Power Gamer,

Thank you for your clear definition in the change of behaviour.

I agree with you that your suggested new behaviour is more appropriate then the current one. 

Verified as a feature request, since this represents a change of behaviour in the GA version.

Just wanted to note that in my previous comment my last statement is actually incorrect:

** That new behavior can be implemented as "convert all date-times to UTC from currently set timezone, perform additions and subtractions and convert result back to currently set time zone". **

The date-time arithmetic cannot be implemented that way because it will produce incorrect results if performed over the physical moment of time when DST change occurred. For example (arithmetic over a day when clock was moved forward): Europe/Kiev 2019-03-31 00:00:00 + 1 DAY should be Europe/Kiev 2019-04-01 00:00:00. If performed by first converting to UTC, adding a day and then converting back to Europe/Kiev the result would be different:
Europe/Kiev 2019-03-31 00:00:00 -> UTC 2019-03-30 22:00:00
UTC 2019-03-30 22:00:00 +1 DAY = UTC 2019-03-31 22:00:00
UTC 2019-03-31 22:00:00 -> Europe/Kiev 2019-04-01 01:00:00

So to produce correct results the arithmetic will have to be implemented directly in the currently set time zone taking into account DST clock movements.

Thanks also for the last comment, especially last paragraph.