What datatype to use for ISO 8601 dates?

Question

I am redesigning a database that currently imports ISO 8601 dates in the format of 2012-10-27T07:30:38+00:00 into a varchar field. The current database is hosted on a MySQL 5.5 server.

In searching through documentation and various SO posts, I have not found a definitive answer on what datatype I should use in MySQL for this purpose. The closest post is: MySQL insert to DATETIME: is it safe to use ISO::8601 format? where it provides a work around of sorts, however this is not a desirable option.

The MySQL documentation (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html) does not say, and the only reference I can find on official documentation is located on page: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

which states "The possible values for the first and second arguments result in several possible format strings (for the specifiers used, see the table in the DATE_FORMAT() function description). ISO format refers to ISO 9075, not ISO 8601."

Now the PostgreSQL documentation specifically mentions ISO8601 ( http://www.postgresql.org/docs/9.2/static/datetime-keywords.html and http://www.postgresql.org/docs/9.2/static/datatype-datetime.html ) which leads me to my question:

Does MySQL correctly support ISO 8601, or should I consider a database with native support?

--Edit--

Attempting to insert the example time stamp above into a datetime column gives the following error:

10:55:55    insert into test(date1) values('2012-10-27T07:30:38+00:00') Error Code: 1292. Incorrect datetime value: '2012-10-27T07:30:38+00:00' for column 'date1' at row 1 0.047 sec

Answer 1

The ISO 8601 date format, "YYYY-MM-DD", is what MySQL emits internally when displaying date values and it can import them just the same.

These are preferable to "American style" dates like "MM/DD/YY" where there's too much ambiguity to be automatically resolved.

ISO 9075 appears to refer to the entirety of the SQL standard, not a specific date format, though the standard itself does have standard formatting for dates and times.

Answer 2

Do not store date or timestamp values in a varchar column. You have no way of ensuring that a correct value is stored (no one prevents you from storing 2012-02-31 28:99:70 in there.

If you don't need the time part, use a date datatype (available in MySQL and PostgreSQL) if you do need the time use a timestamp (or datetime in MySQL) data type.

The formatting of the values should be done in your frontend or if you absolutely have to do it in SQL using e.g. to_char() (or the equivalent in MySQL) when retrieving the values.

Again: never store dates or timestamps in a varchar (just as you should never stored real numbers in a varchar column).

Here is the MySQL overview for date/time datatypes: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html

Here is the PostgreSQL overview for date7time datatypes: http://www.postgresql.org/docs/current/static/datatype-datetime.html

Edit

If you are concerned about the literal format. Both DBMS support the standard ANSI Date and Timestamp literals:

insert into some_table 
   (ts_column, date_column)
values
   (timestamp '2012-10-27T07:30:38+00:00', DATE '2012-12-30');

SQLFiddle for PostgreSQL: http://sqlfiddle.com/#!12/cdd39/1

Note the keywords timestamp and date in front of the character literal.

Edit 2

It seems MySQL cannot insert such a value, although it can use that literal in a SELECT statement: