Multiple columns having default TIMESTAMP

Based on the last two statements in the above section, we can get a workaround for this (date_modified, date_modified) combination problem, where both the columns should default to sysdate.

Follow the below example.

mysql> create table multicolsdefaultts (name varchar(100),date_modified timestamp,date_added timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table multicolsdefaultts;

CREATE TABLE `multicolsdefaultts` (
  `name ` varchar(100) NULL,
  `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `date_added` timestamp NOT NULL default '0000-00-00 00:00:00'
)

mysql> insert into multicolsdefaultts values ('sujay', null,null);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+-------+---------------------+---------------------+
| name  | date_modified       | date_added          |
+-------+---------------------+---------------------+
| sujay | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update multicolsdefaultts set name='sujay1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay1 | 2007-01-09 23:52:44 | 2007-01-09 23:52:31 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into multicolsdefaultts(name) values ('Andale');
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay  | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
+--------+---------------------+---------------------+

mysql> insert into multicolsdefaultts(name, date_added) values ('Andale2', null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+---------+---------------------+---------------------+
| name    | date_modified       | date_added          |
+---------+---------------------+---------------------+
| sujay   | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale  | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
| Andale2 | 2007-01-30 17:12:01 | 2007-01-30 17:12:01 |
+---------+---------------------+---------------------+

The important things to remember here are

• DATE_MODIFIED field should be declared before DATE_ADDED is declared during the table creation. Else provide CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP for the default value.
• The date columns should be inserted with 'NULL' and should not be left out to be filled with default value. If left out then DATE_ADDED will not have appropriate date but "0000-00-00 00:00:00"

• MySQL gives warnings or errors if you try to insert an illegal date. But by using the ALLOW_INVALID_DATES SQL mode, we can still store illegal dates.

•  CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 

  . This allows to update the column to currenttime during row creation as well as updation.