Sunday Feb 15, 2009

Date handling in MySQL

I see my collague Anders wrote some thoughts on date handling in MySQL. I recently had to explain this really down to fine details for a customer, so Anders, everyone else, here's my conclusions...

All the fine details of allowed ways to enter dates are explained here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

With dates, just as in many other cases, MySQL is very flexible in how you can input data. But one thing is that, between the multitudes of formats that are allowed, MySQL does \*not\* by default allow you to enter a non-existant date, e.g. "31st of February 2009". I know some other DB products allow that - they then store it as a correct date automatically (3rd of March 2009). This is mentioned at the end of the above manual page. It can be enabled with an SQL_MODE setting and I have attached a lengthy example about that separately below (Listing 1).


\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
\* LISTING 1
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Short explanation in English:

1. Create a table with DATE field, verify that a normal date insert works fine.

2. sql_mode is not set, using the default "liberal" mode

3. Insert with id=2 "succeeds" but with a warning. In fact, only a zero value was inserted. This is consistent with sql_mode.

4. When sql_mode is set to one of the "strict" modes, the same insert (id=3) fails with an error. No new row was inserted.

5. Adding the option ALLOW_INVALID_DATES to sql_mode, makes the same insert work (id=4).

6. Note that when selecting, the date is returned in the same invalid form it was inserted. However, if using it in an operation, it is converted to match a valid date forward or backward in time.

mysql> CREATE TABLE t (id INT PRIMARY KEY, d1 DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO t VALUES (1, "2008-12-21");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (2, "2008-02-31");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'd1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT \* FROM t;
+----+------------+
| id | d1 |
+----+------------+
| 1 | 2008-12-21 |
| 2 | 0000-00-00 |
+----+------------+
2 rows in set (0.00 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (3, "2008-02-31");
ERROR 1292 (22007): Incorrect date value: '2008-02-31' for column 'd1' at row 1

mysql> SELECT \* FROM t;
+----+------------+
| id | d1 |
+----+------------+
| 1 | 2008-12-21 |
| 2 | 0000-00-00 |
+----+------------+
2 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| sql_mode | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+---------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------+
| sql_mode | STRICT_ALL_TABLES,ALLOW_INVALID_DATES |
+---------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES (4, "2008-02-31");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT \* FROM t;
+----+------------+
| id | d1 |
+----+------------+
| 1 | 2008-12-21 |
| 2 | 0000-00-00 |
| 4 | 2008-02-31 |
+----+------------+
3 rows in set (0.01 sec)

mysql> SELECT d1 + INTERVAL 0 DAY FROM t;
+---------------------+
| d1 + INTERVAL 0 DAY |
+---------------------+
| 2008-12-21 |
| NULL |
| 2008-03-02 |
+---------------------+
3 rows in set (0.01 sec)

mysql> -- Note: 2008 was a leap year so above is correct!
mysql>

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
\* END OF LISTING 1
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Lessons learned from the example:
1. The MySQL default mode often will continue operations without stopping to an error - this also happens in many other cases than this. In the old days, without the ETL tools we have today, this was probably a very convenient feature if you had to insert "dirty" data into your
Data Warehouse.

2. You may/should consider setting sql_mode='TRADITIONAL' (or another strict mode) to stop and get an error to catch such mistakes.

3. Set sql_mode="ALLOW_INVALID_DATES" to allow out of range dates like is usual on some other DB products.

Links about sql_mode:
http://dev.mysql.com/doc/refman/5.1/en/faqs-sql-modes.html
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

Another thing that may also come up is that Oracle allows dates to be expressed in textual forms, such as "31-jan-2009".

This hasn't been supported in MySQL, and it seems the situation is still the same in 5.1. There actually is a variable "date_format" in place, but in practice you can only set it to values that are equivalent with the default, you cannot really change it:


\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
mysql> show variables like "%date%";
+------------------------------+-------------------+
| Variable_name | Value |
+------------------------------+-------------------+
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |

mysql> SET SESSION date_format="%Y-%M-%d";
ERROR 1231 (42000): Variable 'date_format' can't be set to the value of
'%Y-%M-%d'

mysql> SET SESSION date_format="%Y-%m-%d";
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION date_format="%Y/%m/%d";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT \* FROM t;
+----+------------+
| id | d1 |
+----+------------+
| 1 | 2008-12-21 |
| 2 | 0000-00-00 |
| 4 | 2008-02-31 |
+----+------------+
3 rows in set (0.00 sec)

mysql>
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

You can of course always use the DATE_FORMAT() and STR_TO_DATE() functions explicitly if you have a need to format textual date formats.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date
See also:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_get-format

About

The people of the MySQL Telecom team writes about developments around MySQL and MySQL Cluster and how these products are used by our Communitcations industry customers. (Image jasmic@Flickr)

Search

Archives
« February 2009 »
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
       
       
Today