Date and Time in MySQL 5

Tuesday Jan 17th 2006 by Ian Gilfillan
Share:

MySQL 5 has significantly improved the flexibility of its date handling. There have been a few significant changes you will need to be aware of, including some potentially nasty gotchas for users more familiar with the old ways.

Introduction

In April 2003, I wrote two articles on MySQL's Date and Time functions (part1 and part2). With MySQL 5 now released, and in widespread use, it is time for an update. Much remains the same, but there have been a few significant changes you will need to be aware of. This article, which should be read in conjunction with those earlier articles, highlights the changes.

Date and Time types

The following date and time types are available in MySQL 5:

DATE

'YYYY-MM-DD'

The permitted range is from '1000-01-01' to '9999-12-31'. Can be assigned as a string or numeric.

TIME

'HH:MM:SS'

Range from '-838:59:59' to '838:59:59'. Can be assigned as a string or numeric.

DATETIME

'YYYY-MM-DD HH:MM:SS'

Range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Can be assigned as a string or numeric.

YEAR

'YYYY'

The default is for the YEAR to be 4-digits - YEAR(4), but a 2-digit year is also permitted - YEAR(2), although there should be no reason to want to use it. In 4-digit format, YEAR's can range from 1901 to 2155. In 2-digit format, YEAR's can range from 70 (1970) to 69 (2069). Can be assigned as a string or numeric.

TIMESTAMP

'YYYY-MM-DD HH:SS:MM'

Range from '1970-01-01 00:00:00' to partway through the year 2037.

TIMESTAMP changes in MySQL 5

The big change here (since MySQL 4.1) is with the TIMESTAMP type. The old MySQL 4.0 TIMESTAMP was stored in the format YYYYMMDDHHMMSS. By defining it as TIMESTAMP(x) - x being one of 14,12,10,8,6,4 or 2, you could determine in what format the data was returned. The dubious merits of this are no longer available. The TIMESTAMP type is now effectively a TIMESTAMP(19), and the format in which it's returned cannot be changed. Let's create a sample table and explore this further.

mysql> CREATE TABLE dt(date1 DATE,time1 TIME, timestamp1 TIMESTAMP, timestamp2 TIMESTAMP);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO dt(date1,time1) VALUES ('2006-01-03','15:26: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 15:29:03
timestamp2: 0000-00-00 00:00:00
1 row in set (0.00 sec)

As can be seen by the above example, multiple TIMESTAMP's in the same table behave differently. The table definition can shed some light on the mystery.

mysql> DESC dt\G
*************************** 1. row ***************************
  Field: date1
   Type: date
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: time1
   Type: time
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: timestamp1
   Type: timestamp
   Null: YES
    Key:
Default: CURRENT_TIMESTAMP
  Extra:
*************************** 4. row ***************************
  Field: timestamp2
   Type: timestamp
   Null: YES
    Key:
Default: 0000-00-00 00:00:00
  Extra:
4 rows in set (0.01 sec)

The first TIMESTAMP is by default set with a default value of CURRENT_TIMESTAMP. Any other TIMESTAMP's are set with a default value of 0. You can of course explicitly set the defaults if you want this behavior to change. However, the DESC statement hasn't revealed everything. Let's look at the CREATE statement:

mysql> SHOW CREATE TABLE dt\G
*************************** 1. row ***************************
       Table: dt
Create Table: CREATE TABLE 'dt' (
  'date1' date default NULL,
  'time1' time default NULL,
  'timestamp1' timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  'timestamp2' timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

An important attribute reveals itself. on update CURRENT_TIMESTAMP means that anytime a record is UPDATEd as well as INSERTed, it will be populated with the current date and time. It is important to note the difference between the two TIMESTAMP fields, even though their definitions were identical when you CREATEd them. Let's see an UPDATE in action.

mysql> UPDATE dt SET time1='15:44:00'\G
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:44:00
timestamp1: 2006-01-03 15:44:18
timestamp2: 0000-00-00 00:00:00
1 row in set (0.01 sec)

Prior to MySQL 4.1.2, a default for the first TIMESTAMP was always ignored - it was always set to CURRENT_TIMESTAMP. A TIMESTAMP, no matter what the default, can of course always be set explicitly. Assigning it a NULL always populates it with the current date and time, while you can also assign a date and time value.

mysql> UPDATE dt SET timestamp1=NULL,timestamp2=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:44:00
timestamp1: 2006-01-03 16:00:58
timestamp2: 2006-01-03 16:00:58
1 row in set (0.00 sec)
mysql> UPDATE dt SET timestamp1='2006-01-03 16:01:02',timestamp2='2006-01-03 16:01:02';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:44:00
timestamp1: 2006-01-03 16:01:02
timestamp2: 2006-01-03 16:01:02
1 row in set (0.01 sec)

Let's look at a few more potentially confusing subtleties. When you explicitly define a default, but leave out the on update attribute, it is not silently added, as with the previous example. Similarly, when you explicitly define an on update attribute, the default is not set to CURRENT_TIMESTAMP, rather to 0. This is rather non-intuitive behavior, probably designed so that TIMESTAMP behavior didn't change much from earlier versions, but could lead to endless confusion when seemingly identical definitions result in different behavior, especially since this information is not returned by the DESC statement! Here are some examples of MySQL silently behaving differently to what we saw above.

mysql> CREATE TABLE dt3 (id INT, timestamp1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW CREATE TABLE dt3\G
*************************** 1. row ***************************
       Table: dt3
Create Table: CREATE TABLE `dt3` (
  `id` int(11) default NULL,
  `timestamp1` timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> CREATE TABLE dt4 (id INT, timestamp1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
 
mysql> SHOW CREATE TABLE dt4\G
*************************** 1. row ***************************
       Table: dt4
Create Table: CREATE TABLE `dt4` (
  `id` int(11) default NULL,
  `timestamp1` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> CREATE TABLE dt3 (id INT, timestamp1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE dt3\G
*************************** 1. row ***************************
       Table: dt3
Create Table: CREATE TABLE 'dt3' (
  'id' int(11) default NULL,
  'timestamp1' timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> CREATE TABLE dt4 (id INT, timestamp1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE dt4\G
*************************** 1. row ***************************
       Table: dt4
Create Table: CREATE TABLE 'dt4' (
  'id' int(11) default NULL,
  'timestamp1' timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved