| Bug #38459 | Datetime precision lost when saving to VARCHAR | ||
|---|---|---|---|
| Submitted: | 30 Jul 2008 13:37 | Modified: | 14 Jun 2012 12:26 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1, etc | OS: | Any |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[20 Jan 2011 12:58]
Manyi Lu
This is related to/part of WL#946, a runtime task. Manyi
[14 Jun 2012 7:59]
Alexander Barkov
This problem was fixed in 5.6 when full fractional second support was added.
mysql> drop table if exists t1; create table t1 (a varchar(255)); insert into
t1 values ('2008-07-30 16:30:39.123'),('2008-07-30 16:30:39.123' + interval 3
day), (concat('','2008-07-30 16:30:39.123' +interval 3 day)); select * from
t1;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
+----------------------------+
| a |
+----------------------------+
| 2008-07-30 16:30:39.123 |
| 2008-08-02 16:30:39.123000 |
| 2008-08-02 16:30:39.123000 |
+----------------------------+
3 rows in set (0.00 sec)
[14 Jun 2012 12:27]
Alexander Barkov
A minor clarification: the exact version number is 5.6.4.
Description: In-memory precision datetime type get truncated, when written to field types, which have enough of storage, though casts to string retain the value. This is regression in 5.0/5.1, it works properly in 4.1. How to repeat: mysql> create table texttable( a varchar(255));Query OK, 0 rows affected (0.06 sec) mysql> insert into texttable values ('2008-07-30 16:30:39.123'),('2008-07-30 16:30:39.123' + interval 3 day), (concat('','2008-07-30 16:30:39.123' + interval 3 day)); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from texttable; +----------------------------+ | a | +----------------------------+ | 2008-07-30 16:30:39.123 | | 2008-08-02 16:30:39 | | 2008-08-02 16:30:39.123000 | +----------------------------+ 3 rows in set (0.00 sec) Suggested fix: avoid stripping data when it won't be truncated anyway.