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

Comments:

I don't use the DATE or DATETIME types anymore. I just store the UNIX timestamp an integer column. In many cases I can use less bytes. I do not really understand why a database should a timezone support... Isn't presenting a local well formatted date a job for the presentational layer?

Posted by Flavio on February 15, 2009 at 05:38 PM EET #

Hi Henrik,

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

So my biggest beef with this one is that is actually still allows 'invalid' dates - it just won't let you insert/update them. This actually holds for most of the "restrictions" enabled by the "strict" modes. To some extent they keep you from entering bad data but not from retrieving it. But lets forget about that for a moment. Just consider this:

mysql> set @@sql_mode := 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> create table ts (id int, ts1 timestamp, ts2 timestamp);
ERROR 1067 (42000): Invalid default value for 'ts2'
mysql> set @@sql_mode := '';
Query OK, 0 rows affected (0.00 sec)

mysql> create table ts (id int, ts1 timestamp, ts2 timestamp);
Query OK, 0 rows affected (0.01 sec)

OK - so far, so good - we do a show create table and we see:

CREATE TABLE `ts` (
`id` int(11) DEFAULT NULL,
`ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1

So apparently TIMESTAMP is like that. No big deal. We continue:

mysql> set @@sql_mode := 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

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

Note there is no warning and no error....wondering what's in the ts2 column?

mysql> select \* from ts;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2009-02-16 02:20:35 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

So it this really the strict mode?

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode
|
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
|
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

yup...

To be fair this is 5.1.30. Anyone here to prove it is fixed in 5.1.31?

Posted by Roland Bouman on February 15, 2009 at 07:30 PM EET #

My point was that a date such as Feb 31, for example, really might be valid in some contexts, and is NOT the same as March 3, as you say. (or Feb 2 if it is a leap year). Feb 30 really IS a valid data, is in February and should NOT be converted to a date in March.

Look at your interest payments, if you have any, they are THE SAME in every month, despite that February, or any other month, isn't really 1/12 of a year long! And if you, on, say March 2, pay off all your debts, then when is the last month you pay interest? February (As March 2 is February 30 or 31)? Or March (as this is in March, in the calendar)?

/Karlsson
Getting all messed up....

Posted by Anders Karlsson on February 16, 2009 at 02:51 AM EET #

Ah Anders, I see. In fact, as we notice above, MySQL does not convert the dates stored - but you couldn't do any SQL arithmetic on them, then they would be converted (unless you can switch to some non-standard calendars that I'm not aware of). But of course you can do such arithmetics in your application, and MySQL will not have auto-converted the date to anything for you.

In fact, you have just explained to me the reason why MySQL doesn't do that. Thanks!

Posted by Henrik Ingo on February 16, 2009 at 03:14 AM EET #

Henrik, what about my example? Surely this is a bug?

Posted by Roland Bouman on February 16, 2009 at 08:07 AM EET #

Heh, Roland: I would take \*your\* advice about things like this any day :-)

FWIW, I vote for bug, anyone else? But otoh, I know of other cases too, where if you create a table with one mode and then do things in another mode, weird things will happen, things will disappear, etc...

Posted by Henrik Ingo on February 16, 2009 at 08:36 AM EET #

@Flavio: Unix timestamp is indeed a good approach - and no, quite frankly I don't think storing the timezone information should be a main concern either. Sometimes of course a database may need to store dates outside the range of the Unix timestamp, eg about 50% of people's birthdays are before 1970. But when that is not the case, then of course using the Unix timestamp is efficient.

Of course, you don't need to use INT, you can also use MySQL's TIMESTAMP format for that.

Posted by Henrik Ingo on February 16, 2009 at 03:34 PM EET #

Ingo, the default is to accept invalid dates, as the compiled-in default for sql_mode is empty.
Then if you install on Windows, the configuration wizard by default sets strict (TRADITIONAL) mode so indeed there it won't - but that's only because of the Windows installer config wizard's created config.

Posted by Arjen Lentz on February 20, 2009 at 03:35 PM EET #

Post a Comment:
Comments are closed for this entry.
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
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today