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:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0, 5.1, etc OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any

[30 Jul 2008 13:37] Domas Mituzas
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.
[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.