Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Jan 17, 2006

Date and Time in MySQL 5 - Page 2

By Ian Gilfillan

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date