[MDEV-25279] Time operations do not consider daylight savings Created: 2021-03-28  Updated: 2021-05-13  Resolved: 2021-05-13

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Time zones
Affects Version/s: 10.3.27
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Marc Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 0
Labels: None
Environment:

linux

Issue Links:
Duplicate
is duplicated by MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temp... Stalled

 Description   

NOW()+INTERVAL 1800 SECOND produces invalid date.
Having a table with a column
`valid` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

and an insert statement like this
REPLACE INTO tblCaptcha SET `valid`=NOW()+INTERVAL 1800 SECOND

produces the following error at 2021-03-28, 01:30:08
Incorrect datetime value: '2021-03-28 02:00:08' for column `tblCaptcha`.`valid` at row 1

 Comments   
Comment by Marc [ 2021-05-07 ]

Anything new on this issue? This data manipulation can happen at any time of the year. even though it is more likely when time savings is adjusted.
I'm not sure how to handle duplicate time stamp of 2:30 in october, when time is set back from 3:00 to 2:00. At least if "NOW" is involved, the time can be set to the correct internal representation and should not be the same as writing "2021-10-31 02:30:01"

Comment by Sergei Golubchik [ 2021-05-13 ]

The problem is that NOW() (which is a TIMESTAMP) is converted to a DATETIME for temporal arithmetic. Datetime value 2021-03-28 02:00:08 is perfectly valid. But it cannot be converted back to a timestamp again in your time zone.

This will be fixed in MDEV-15750. Unfortunately, it's a rather big change, so might take a while.

As a workaround, until MDEV-15750 is implemented, you can do your temporal arithmetic in timestamps, like

REPLACE INTO tblCaptcha SET `valid`=FROM_UNIXTIME(UNIX_TIMESTAMP()+ 1800);

Generated at Thu Feb 08 09:36:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.