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:

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)
Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles