The effects of the SQL MODE on date and
time behavior
The concept of a
TIMESTAMP is rather different in MaxDB. With MySQL AB working towards greater
integration between MaxDB and 'ordinary' MySQL, you can now run MySQL in MAXDB
mode. In this mode, all TIMESTAMP fields are treated as ordinary DATETIME
fields. Let's run the identical commands we ran earlier, but this time in MAXDB
mode. We will change to MAXDB mode (and the later modes that follow) just for
the particular connection we're running. Quitting and reconnecting will restore
all defaults.
mysql> SET SESSION sql_mode=MAXDB;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE dt5(date1 DATE,time1 TIME, timestamp1 TIMESTAMP, timestamp2 TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt5(date1,time1) VALUES ('2006-01-03','15:26:02');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt5\G
*************************** 1. row ***************************
date1: 2006-01-03
time1: 15:26:02
timestamp1: NULL
timestamp2: NULL
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE dt5\G
*************************** 1. row ***************************
Table: dt5
Create Table: CREATE TABLE "dt5" (
"date1" date default NULL,
"time1" time default NULL,
"timestamp1" datetime default NULL,
"timestamp2" datetime default NULL
)
1 row in set (0.00 sec)
There is no difference
between the first and second TIMESTAMP instances, and both fields are defaulted
to NULL, with no on update.
You don't need to worry that you will experience problems with previously
defined TIMESTAMPs though - since the only real difference now between a
DATETIME and a TIMESTAMP is in their default definitions, existing TIMESTAMPS
will still behave as expected.
mysql> INSERT INTO dt(date1,time1) VALUES('2006-01-03','17:44:02');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
date1: 2006-01-03
time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
date1: 2006-01-03
time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
2 rows in set (0.00 sec)
The TIMESTAMPs are set to
the expected defaults, as before.
Another important change
is that before MySQL 5.0.2, day and month values did not have to be legal,
merely in the range 0-12, and 1-31. So, dates such as the 31st of February
could be safely stored. By default, these are now converted to 0, and throw a
warning.
mysql> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:03');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
date1: 2006-01-03
time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
date1: 2006-01-03
time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
date1: 0000-00-00
time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
3 rows in set (0.00 sec)
You can revert to the old
behavior with the ALLOW_INVALID_DATES
mode.
mysql> SET SESSION sql_mode=ALLOW_INVALID_DATES;
Query OK, 0 rows affected (0.00 sec)
> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:04');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
date1: 2006-01-03
time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
date1: 2006-01-03
time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
date1: 0000-00-00
time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
*************************** 4. row ***************************
date1: 2005-02-31
time1: 18:04:00
timestamp1: 2006-01-03 18:04:36
timestamp2: 0000-00-00 00:00:00
4 rows in set (0.00 sec)
If allowing invalid dates
at all disconcerts you, you can enable one of the strict modes (either STRICT_ALL_TABLES or STRICT_TRANS_TABLES), which will result
in MySQL throwing an error if the date is at all invalid.
mysql> SET SESSION sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:06');
ERROR 1292 (22007): Incorrect date value: '2005-02-31' for column 'date1' at row 1
Strict mode still permits
zero dates though. To prohibit these, you can use the NO_ZERO_DATE (the entire date cannot be
zero) or NO_ZERO_IN_DATE (no
part of the date can be zero) modes. A convenient mode to use in this case is TRADITIONAL, which is equivalent to all
of the STRICT_TRANS_TABLES,
STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO
and NO_AUTO_CREATE_USER
modes.
mysql> INSERT INTO dt(date1,time1) VALUES('0000-00-00','18:22');
Query OK, 1 row affected (0.00 sec)
mysql> SET SESSION sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt(date1,time1) VALUES('0000-00-00','18:23');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date1' at row 1
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
date1: 2006-01-03
time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
date1: 2006-01-03
time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
date1: 0000-00-00
time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
*************************** 4. row ***************************
date1: 2005-02-31
time1: 18:04:00
timestamp1: 2006-01-03 18:04:36
timestamp2: 0000-00-00 00:00:00
*************************** 5. row ***************************
date1: 0000-00-00
time1: 18:22:00
timestamp1: 2006-01-03 18:22:03
timestamp2: 0000-00-00 00:00:00
5 rows in set (0.00 sec)
Conclusion
MySQL 5 has significantly
improved the flexibility of its date handling. We will also see further
examples of this in future columns. However, as we have seen, there are some
potentially nasty gotchas for users more familiar with the old ways. The
changes though do in general encourage better practices, and should be welcomed
by MySQL users.
»
See All Articles by Columnist Ian Gilfillan
» 
Comment and Contribute
