Date and Time in MySQL 5

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:

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)