MySQL Bugs: #12650: New DATE TIME & TIMESTAMP format

Bug #12650	New DATE TIME & TIMESTAMP format
Submitted:	18 Aug 2005 15:38	Modified:	25 Aug 2005 10:42
Reporter:	Keith Roberts	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.x, 4.1.x, 5.0.x	OS:	Linux (Linux, Windows, Any)
Assigned to:		CPU Architecture:	Any

Description:
Ref: DATETIME, DATE and TIMESTAMP values expressed in the
following way.

Instead of:

As a string in either 'YYYY-MM-DD HH:MM:SS' 
or 'YY-MM-DD HH:MM:SS' format. 

As a string with no delimiters in either 'YYYYMMDDHHMMSS' 
or 'YYMMDDHHMMSS' format, provided that the string makes 
sense as a date.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS
format, provided that the number makes sense as a date.

As a number in either YYYYMMDD or YYMMDD format, provided
that the number makes sense as a date.

***

Would it not be feasable to express the above DATETIME, DATE
and TIMESTAMP values in a more natural form that people are
used to dealing with, such as:

As a string in either 'HH:MM:SS DD-MM-YYYY' 
or 'HH:MM:SS DD-MM-YY' format. 

As a string with no delimiters in either 'HHMMSSDDMMYYYY' 
or 'HHMMSSDDMMYY' format, provided that the string makes 
sense as a date.

As a number in either HHMMSSDDMMYYYY or HHMMSSDDMMYY
format, provided that the number makes sense as a date.

As a number in either DDMMYYYY or DDMMYY format, provided
that the number makes sense as a date.

And then allow MySQL to detect and convert the new DATETIME
and DATE format values to its usual form for storing in the
database, at INSERT or UPDATE time. Table creation time I can
live with at the moment, as that is only a one-off thing.

If the DATETIME, DATE and TIMESTAMP's are provided in the current
format, then they could be stored as is, without any 'flipping' of
the new-style year and date values.

I (and many others as well, I'm sure) would find this new date
and time format alot easier to work with.

A configuration option could also be added in my.ini, to allow
enabling/disabling of this feature.

Keith Roberts

How to repeat:
N/A - feature request

Thank you for your suggestion. But MySQL's current default format (unlike many other's) is based on SQL standard. A small quote for you from the draft of SQL 2003 Standard:

"<date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>"

So, I changed "bug type" to feature request, but I do not believe this format will be changed.

You should use date and time functions to present (insert) date values in the format more appropriate for you: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html