Bug #77232 Microtime not supported for <1 second past epoch

Submitted: 2 Jun 2015 20:38 Modified: 3 Jun 2015 7:07
Reporter: Trey Raymond Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.23, 5.6.24, 5.6.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, microtime, time

[2 Jun 2015 20:38] Trey Raymond
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.
[3 Jun 2015 7:07] MySQL Verification Team
Hello Trey Raymond,

Thank you for the report and test case.

Thanks,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.