Bug #12268 Datetime column width wrong with in numeric context
Submitted: 29 Jul 2005 12:20 Modified: 21 Jan 2006 18:41
Reporter: Richard Sonnen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1.13, 4.1.14-nt OS:FreeBSD (FreeBSD 4.11p8, Windows)
Assigned to: Bugs System CPU Architecture:Any
Triage: D4 (Minor)

[29 Jul 2005 12:20] Richard Sonnen
Description:
In 4.1.12, a DATETIME column in numeric context return in YYYYMMDDHHMMSS format.  As of 4.1.13 the same column will return in YYYYMMDDHHMMSS.FFFFFF where F appears to be a fractional second.

My understanding was that fractional seconds were going to be supported in a future release, but not in this one, as they aren't mentioned in the changelog (http://dev.mysql.com/doc/mysql/en/news-4-1-13.html).

How to repeat:
mysql> create table datetest (start_time datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into datetest values(now());
Query OK, 1 row affected (0.00 sec)

mysql> select start_time from datetest;
+---------------------+
| start_time          |
+---------------------+
| 2005-07-29 06:02:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> select start_time +0 from datetest;
+-----------------------+
| start_time +0         |
+-----------------------+
| 20050729060254.000000 |
+-----------------------+
1 row in set (0.00 sec)

Suggested fix:
Return column width to previous format, or document new behavior in the release notes along with a recommended workaround to mimic the old behavior.  (Casting the value to an unsigned integer will do it, but seems clumsy.)
[29 Jul 2005 13:30] Alexander Keremidarski
Verified with 4.1 from BK tree

ChangeSet@1.2354, 2005-07-28 03:22:47+03:00, monty@mysql.com
[10 Aug 2005 16:08] Patrick Galbraith
I'm wondering if then, that this just needs to be documented?
[3 Sep 2005 23:51] Patrick Galbraith
Isolated this to a change from 5.0.7 to 5.0.8
[6 Sep 2005 0:13] Patrick Galbraith
I will be working on this bug 8th and 9th - do not take from me! ;)
[20 Sep 2005 10:14] Valeriy Kravchuk
The same behaviour in 4.1.14:

mysql> select start_time from datetest;
+---------------------+
| start_time          |
+---------------------+
| 2005-09-20 13:15:22 |
+---------------------+
1 row in set (0.00 sec)

mysql> select start_time +0 from datetest;
+-----------------------+
| start_time +0         |
+-----------------------+
| 20050920131522.000000 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)
[20 Oct 2005 17:31] Patrick Galbraith
In talking to Brian and Monty:

<monty|zzz>	The plan is, as stated in the bug report that when converting a datetime to number, we will also include sub seconds
[04:33] 	<patg>	I'm wondering where I should focus my examination on, codewise. Also, that sounds like a bug to you too?
[04:34] 	<monty|zzz>	we don't yet have sub seconds support, but the question is when to start adding the .000000 data
[04:34] 	<monty|zzz>	I am not 100 % sure it's a bug as this is something we want to do in the future as soon as we have datetime with higher precission
[04:34] 	<monty|zzz>	But I don't feel strongly about this
[04:35] 	<patg>	I see, so the real problem is that it subseconds don't exist, but are being displayed.
[04:35] 	<patg>	ok, antony just came down and everyone is hovering over me because they are hungry for dinner!
[04:35] 	<patg>	monty|zzz: thanks
[04:36] 	<monty|zzz>	yes. In 5.2 or 5.3 we should add sub second support, the question is if it's good to prepare users for that

Brian, says that it's the behaviour (it should be case as a float vs. int) that we want, and that it just needs to be documented.
[20 Oct 2005 20:19] Paul Dubois
Additional note: The change occurred in 4.1.13 for 4.1
and 5.0.8 for 5.0.
[19 Jan 2006 21:46] Mike Hillyer
Changing this to a documentation bug with Verified status as the Documenting status is reserved for bugfixes that need changelog entries.
[20 Jan 2006 17:45] Paul Dubois
Documenting this is hung up waiting for Bug#16546 to be resolved.
[21 Jan 2006 18:41] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've added incompatibility notes to the 4.1.13 and 5.0.8 changelogs,
and to the 4.1 and 5.0 upgrading sections in their respective manuals.
Bug#16546 still needs to be resolved..