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 dtG *************************** 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 dtG *************************** 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 dtG *************************** 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 dtG *************************** 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 dtG *************************** 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 dtG *************************** 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 dt3G
*************************** 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 dt4G
*************************** 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 dt3G *************************** 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 dt4G *************************** 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)