Description:
Timestamp fields with microtime support truncate partial values <1 to 0, with a warning. It should store like '1970-01-01 00:00:00.012345' to avoid data loss.
Not much impact to prod apps, few people deal with that second of time in real life, but some benchmark and testing tools use low int ranges for sample times, and fail on this.
How to repeat:
mysql> CREATE TABLE `ts` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `ts` timestamp(6) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> select from_unixtime(0.012345);
+----------------------------+
| from_unixtime(0.012345) |
+----------------------------+
| 1970-01-01 00:00:00.012345 |
+----------------------------+
1 row in set (0.00 sec)
mysql> insert into ts set ts=from_unixtime(0.012345);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from ts;
+----+----------------------------+
| id | ts |
+----+----------------------------+
| 1 | 0000-00-00 00:00:00.000000 |
+----+----------------------------+
1 row in set (0.00 sec)
mysql> insert into ts set ts=from_unixtime(1.012345);
Query OK, 1 row affected (0.00 sec)
mysql> select * from ts;
+----+----------------------------+
| id | ts |
+----+----------------------------+
| 1 | 0000-00-00 00:00:00.000000 |
| 2 | 1970-01-01 00:00:01.012345 |
+----+----------------------------+
2 rows in set (0.00 sec)
mysql> insert into ts set ts='1970-01-01 00:00:00.012345';
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from ts;
+----+----------------------------+
| id | ts |
+----+----------------------------+
| 1 | 0000-00-00 00:00:00.000000 |
| 2 | 1970-01-01 00:00:01.012345 |
| 3 | 0000-00-00 00:00:00.000000 |
+----+----------------------------+
3 rows in set (0.00 sec)
Suggested fix:
Fix the code that's truncating it, looks like there's an if check before the microtime support code comes around.
Description: Timestamp fields with microtime support truncate partial values <1 to 0, with a warning. It should store like '1970-01-01 00:00:00.012345' to avoid data loss. Not much impact to prod apps, few people deal with that second of time in real life, but some benchmark and testing tools use low int ranges for sample times, and fail on this. How to repeat: mysql> CREATE TABLE `ts` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `ts` timestamp(6) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> select from_unixtime(0.012345); +----------------------------+ | from_unixtime(0.012345) | +----------------------------+ | 1970-01-01 00:00:00.012345 | +----------------------------+ 1 row in set (0.00 sec) mysql> insert into ts set ts=from_unixtime(0.012345); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'ts' at row 1 | +---------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from ts; +----+----------------------------+ | id | ts | +----+----------------------------+ | 1 | 0000-00-00 00:00:00.000000 | +----+----------------------------+ 1 row in set (0.00 sec) mysql> insert into ts set ts=from_unixtime(1.012345); Query OK, 1 row affected (0.00 sec) mysql> select * from ts; +----+----------------------------+ | id | ts | +----+----------------------------+ | 1 | 0000-00-00 00:00:00.000000 | | 2 | 1970-01-01 00:00:01.012345 | +----+----------------------------+ 2 rows in set (0.00 sec) mysql> insert into ts set ts='1970-01-01 00:00:00.012345'; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'ts' at row 1 | +---------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from ts; +----+----------------------------+ | id | ts | +----+----------------------------+ | 1 | 0000-00-00 00:00:00.000000 | | 2 | 1970-01-01 00:00:01.012345 | | 3 | 0000-00-00 00:00:00.000000 | +----+----------------------------+ 3 rows in set (0.00 sec) Suggested fix: Fix the code that's truncating it, looks like there's an if check before the microtime support code comes around.