Description:
Date_add() is not correct if resulting time will jump over changing to daylight saving time.
"2009-03-29 01:59:59" is a time before switching to Daylight Time (summer time) in Russia and Europe. In next second time must shift for 1 hour and time must be "2009-03-29 03:00:00".
But if we'll use date_add() to add time period we'll have incorrect value.
How to repeat:
Next procedure adds 3 seconds in different ways.
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`testtimeshift` $$
CREATE PROCEDURE `test`.`testtimeshift` ()
BEGIN
DECLARE dt DATETIME;
DECLARE ts TIMESTAMP;
DECLARE ts_conv int;
DECLARE sec int;
DECLARE sec_conv TIMESTAMP;
DECLARE num int;
set ts = "2009-03-29 01:59:56";
set dt = ts;
set sec = unix_timestamp(ts);
set num = 5;
while num > 0 do
set ts_conv = unix_timestamp(ts);
set sec_conv = from_unixtime(sec);
SELECT dt,ts,ts_conv,sec,sec_conv;
set ts = DATE_ADD(ts,INTERVAL 3 SECOND);
set dt = DATE_ADD(dt,INTERVAL 3 SECOND);
set sec = sec + 3;
set num = num-1;
end while;
END $$
DELIMITER ;
######################################
mysql> call test.testtimeshift;
# (I skip some usless lines)
+---------------------+---------------------+------------+------------+---------------------+
| dt | ts | ts_conv | sec | sec_conv |
+---------------------+---------------------+------------+------------+---------------------+
| 2009-03-29 01:59:56 | 2009-03-29 01:59:56 | 1238281196 | 1238281196 | 2009-03-29 01:59:56 |
+---------------------+---------------------+------------+------------+---------------------+
| 2009-03-29 01:59:59 | 2009-03-29 01:59:59 | 1238281199 | 1238281199 | 2009-03-29 01:59:59 |
+---------------------+---------------------+------------+------------+---------------------+
| 2009-03-29 02:00:02 | 2009-03-29 03:00:00 | 1238281200 | 1238281202 | 2009-03-29 03:00:02 |
+---------------------+---------------------+------------+------------+---------------------+
| 2009-03-29 02:00:05 | 2009-03-29 03:00:03 | 1238281203 | 1238281205 | 2009-03-29 03:00:05 |
+---------------------+---------------------+------------+------------+---------------------+
| 2009-03-29 02:00:08 | 2009-03-29 03:00:06 | 1238281206 | 1238281208 | 2009-03-29 03:00:08 |
+---------------------+---------------------+------------+------------+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1299 | Invalid TIMESTAMP value in column 'ts' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
######################################
Note changing of time in dt,ts,sec_conv columns after 01:59:59.
+---------------------+---------------------+---------------------+
| dt | ts | sec_conv |
+---------------------+---------------------+---------------------+
| 2009-03-29 02:00:02 | 2009-03-29 03:00:00 | 2009-03-29 03:00:02 |
+---------------------+---------------------+---------------------+
We have three different values for time. But the correct one is only in sec_conv.
In this case I think the good way to add some value to time is convert it to unixtime, add value in seconds and then convert it back.
Some info:
mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | MSD |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
Description: Date_add() is not correct if resulting time will jump over changing to daylight saving time. "2009-03-29 01:59:59" is a time before switching to Daylight Time (summer time) in Russia and Europe. In next second time must shift for 1 hour and time must be "2009-03-29 03:00:00". But if we'll use date_add() to add time period we'll have incorrect value. How to repeat: Next procedure adds 3 seconds in different ways. DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`testtimeshift` $$ CREATE PROCEDURE `test`.`testtimeshift` () BEGIN DECLARE dt DATETIME; DECLARE ts TIMESTAMP; DECLARE ts_conv int; DECLARE sec int; DECLARE sec_conv TIMESTAMP; DECLARE num int; set ts = "2009-03-29 01:59:56"; set dt = ts; set sec = unix_timestamp(ts); set num = 5; while num > 0 do set ts_conv = unix_timestamp(ts); set sec_conv = from_unixtime(sec); SELECT dt,ts,ts_conv,sec,sec_conv; set ts = DATE_ADD(ts,INTERVAL 3 SECOND); set dt = DATE_ADD(dt,INTERVAL 3 SECOND); set sec = sec + 3; set num = num-1; end while; END $$ DELIMITER ; ###################################### mysql> call test.testtimeshift; # (I skip some usless lines) +---------------------+---------------------+------------+------------+---------------------+ | dt | ts | ts_conv | sec | sec_conv | +---------------------+---------------------+------------+------------+---------------------+ | 2009-03-29 01:59:56 | 2009-03-29 01:59:56 | 1238281196 | 1238281196 | 2009-03-29 01:59:56 | +---------------------+---------------------+------------+------------+---------------------+ | 2009-03-29 01:59:59 | 2009-03-29 01:59:59 | 1238281199 | 1238281199 | 2009-03-29 01:59:59 | +---------------------+---------------------+------------+------------+---------------------+ | 2009-03-29 02:00:02 | 2009-03-29 03:00:00 | 1238281200 | 1238281202 | 2009-03-29 03:00:02 | +---------------------+---------------------+------------+------------+---------------------+ | 2009-03-29 02:00:05 | 2009-03-29 03:00:03 | 1238281203 | 1238281205 | 2009-03-29 03:00:05 | +---------------------+---------------------+------------+------------+---------------------+ | 2009-03-29 02:00:08 | 2009-03-29 03:00:06 | 1238281206 | 1238281208 | 2009-03-29 03:00:08 | +---------------------+---------------------+------------+------------+---------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1299 | Invalid TIMESTAMP value in column 'ts' at row 1 | +---------+------+-------------------------------------------------+ 1 row in set (0.00 sec) ###################################### Note changing of time in dt,ts,sec_conv columns after 01:59:59. +---------------------+---------------------+---------------------+ | dt | ts | sec_conv | +---------------------+---------------------+---------------------+ | 2009-03-29 02:00:02 | 2009-03-29 03:00:00 | 2009-03-29 03:00:02 | +---------------------+---------------------+---------------------+ We have three different values for time. But the correct one is only in sec_conv. In this case I think the good way to add some value to time is convert it to unixtime, add value in seconds and then convert it back. Some info: mysql> show variables like "%time_zone"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | MSD | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)