[MDEV-15654] ADDTIME creates invalid timestamp value near DST changes Created: 2018-03-25  Updated: 2020-08-14  Resolved: 2020-08-14

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Swa F Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: timediff, timezones
Environment:

freebsd (from the ports collection)

Issue Links:
Blocks
is blocked by MDEV-13995 MAX(timestamp) returns a wrong result... Closed
is blocked by MDEV-14630 Replace {STRING|INT|REAL|DECIMAL|DATE... Closed
Relates
relates to MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temp... Stalled
relates to MDEV-15751 CURRENT_TIMESTAMP should return a TIM... In Review
Sprint: 10.4.0-1

 Description   

reproducing this is time sensitive. And occurs just once a year ...

Server is set to timezone Europe/Brussels
at 2018-03-25 02:00:00 clocks would move to 03:00:00 (wintertime->summertime changeover) making times between 2AM and 3 AM invalid.

1 hour before this Timezone change I noticed errors inserting records into a table that had a column defined as: 

+-----------+------------------+------+-----+---------------------+-------+
 
| Field     | Type             | Null | Key | Default             | Extra |
 
+-----------+------------------+------+-----+---------------------+-------+
 
[...]
 
| expires   | timestamp        | NO   |     | 0000-00-00 00:00:00 |       |
 
+-----------+------------------+------+-----+---------------------+-------+

The value inserted was calculated on insert by mariadb itself using ADDTIME(NOW(),'01:00:00') as value for the expires field.

Mariadb was using the system Timezones:

MariaDB [(none)]> SELECT @@global.time_zone, @@session.time_zone;
 
+--------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone |
 
+--------------------+---------------------+
 
| SYSTEM             | SYSTEM              |
 
+--------------------+---------------------+
 
1 row in set (0.00 sec)

Essentially ADDTIME creates invalid dates as it doesn't account for timezone changes.
While at the same time (pun not intended) mariadb does enforce the validity of such timestamps when inserted in a field in the database.

MariaDB [adminsite]> select now();
 
+---------------------+
 
| now()               |
 
+---------------------+
 
| 2018-03-25 01:38:44 |
 
+---------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [adminsite]> select ADDTIME(NOW(),'01:00:00');
 
+---------------------------+
 
| ADDTIME(NOW(),'01:00:00') |
 
+---------------------------+
 
| 2018-03-25 02:39:34       |
 
+---------------------------+
 
1 row in set (0.00 sec)

Actual insert error message (Time ran out, sorry, DST change occurred before I could fully document it) example:
PHP message: mysqli::execute: Incorrect datetime value: '2018-03-25 02:29:27' for column 'expires'

Issuing a "set time_zone='+00:00';" before using the database connection from the application worked around the the problem (I don't mind not using local time at all - in fact using localtime only started after a server swap and did go by unnoticed till now).

Still if the database insists on valid time, it should also calculate valid time itself.



 Comments   
Comment by Daniel Black [ 2018-03-25 ]

My local timezone has a 2am to 3am jump at beginning of DST as tested here:

MariaDB [test]>  SELECT @@global.time_zone, @@session.time_zone;           
+--------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone |
 
+--------------------+---------------------+
 
| SYSTEM             | SYSTEM              |
 
+--------------------+---------------------+
 
MariaDB [test]> select @@sql_mode;
 
+-------------------------------------------------------------------------------------------+
 
| @@sql_mode                                                                                |
 
+-------------------------------------------------------------------------------------------+
 
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+-------------------------------------------------------------------------------------------+
 
 
 
MariaDB [test]> show create table mdev15654;
 
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table     | Create Table                                                                                                                                         |
 
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| mdev15654 | CREATE TABLE `mdev15654` (
 
  `t` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select @@version;
 
+--------------------+
 
| @@version          |
 
+--------------------+
 
| 10.3.6-MariaDB-log |
 
+--------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY);
 
+-----------------------------------------------------+
 
| adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY) |
 
+-----------------------------------------------------+
 
| 2018-10-07 01:57:35                                 |
 
+-----------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> insert into mdev15654 values (addtime(adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY), '0:50:00'));
 
ERROR 1292 (22007): Incorrect datetime value: '2018-10-07 02:47:41' for column 't' at row 1

So not fixed in 10.3.

Notably adddate doesn't doesn't perform the same timezone calculations and results in an invalid time:

MariaDB [test]> select adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY);
 
+-----------------------------------------------------+
 
| adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY) |
 
+-----------------------------------------------------+
 
| 2018-10-07 02:01:18                                 |
 
+-----------------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Daniel Black [ 2018-03-25 ]

Confirmed still an error in 10.2

MariaDB [test]> select adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY);                                            
+-----------------------------------------------------+
 
| adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY) |
 
+-----------------------------------------------------+
 
| 2018-10-07 01:11:41                                 |
 
+-----------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> insert into mdev15654 values (addtime(adddate(NOW() + INTERVAL 5 HOUR , INTERVAL 195 DAY), '1:00:00'));
 
ERROR 1292 (22007): Incorrect datetime value: '2018-10-07 02:11:58' for column 't' at row 1
 
MariaDB [test]> select * from mdev15654;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.2.14-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select adddate(NOW() + INTERVAL 6 HOUR , INTERVAL 195 DAY);
 
+-----------------------------------------------------+
 
| adddate(NOW() + INTERVAL 6 HOUR , INTERVAL 195 DAY) |
 
+-----------------------------------------------------+
 
| 2018-10-07 02:14:19                                 |
 
+-----------------------------------------------------+
 
1 row in set (0.00 sec)

adddate still also able to produce invalid values.

Comment by Daniel Black [ 2018-03-25 ]

Related, addtime jumping over such an intervals neglects that there is a gap.

MariaDB [test]> select adddate(NOW() + INTERVAL 4 HOUR , INTERVAL 195 DAY);
 
+-----------------------------------------------------+
 
| adddate(NOW() + INTERVAL 4 HOUR , INTERVAL 195 DAY) |
 
+-----------------------------------------------------+
 
| 2018-10-07 01:06:46                                 |
 
+-----------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select addtime(adddate(NOW() + INTERVAL 4 HOUR , INTERVAL 195 DAY), '1:55:00');
 
+-------------------------------------------------------------------------+
 
| addtime(adddate(NOW() + INTERVAL 4 HOUR , INTERVAL 195 DAY), '1:55:00') |
 
+-------------------------------------------------------------------------+
 
| 2018-10-07 03:01:47                                                     |
 
+-------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Sergei Golubchik [ 2018-04-02 ]

This is a combination of issues.

First, note the difference between a TIMESTAMP and a DATETIME. TIMESTAMP corresponds to some specific point in time in the local time zone. DATETIME is a combination of 6 numbers (year/month/day/hour/minute/second) and is not associated with any time zone.

So, one issue: NOW() returns a datetime, not a timestamp. That is, its result has no time zone information, so 2018-03-25 02:39:34 is a perfectly valid datetime value. It would be logical, I'd say, for NOW() to return a timestamp. I've created MDEV-15751 for this task. But for compatibility reasons we cannot do this change after beta.

Second issue: even if NOW() would've returned a timestamp, ADDTIME would return a datetime, and, again, 2018-03-25 02:39:34 would be perfectly valid. I've created MDEV-15750 to change it, but, again, compatibility issues.

You get an error when this datetime value is stored in a timestamp column, because not every valid datetime value is a valid timestamp value.

Until we fix that all, you can use a workaround. Use from_unixtime(unix_timestamp(now())+3600) — it will correctly move you one hour forward in the local time zone, taking DST into account as needed.

Comment by Sergei Golubchik [ 2020-08-14 ]

At the moment this behaves as expected, even if not ideally.

Various aspects of this behavior will be changed in MDEV-15750 and MDEV-15751

Generated at Thu Feb 08 08:23:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.