Playing with MySQL Datatypes - TimeStamp

MySQL time stamp at times exhibits some strange behaviors and at sometimes confuses people.

1. Create a table with two timestamp columns,
create table aa(i int, t1 timestamp, t2 timestamp);

2. Do an insert into the non timestamp column only
insert into aa(i) values(1);

3. Now what do you expect from a select * from the table?
mysql> select * from aa;
+------+---------------------+---------------------+
| i | t1 | t2 |
+------+---------------------+---------------------+
| 1 | 2011-06-23 22:50:02 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
1 row in set (0.07 sec)

interestingly the table has values not specified and that's also only in the first timestamp column.

** If you want to explore further do an update to the value i and again observe the value in column t1!

4. How did this happen? How did the unspecified column get updated?
show create table aa;
| aa | CREATE TABLE `aa` (
`i` int(11) DEFAULT NULL,
`t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`t2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

observe the part I have emphasized in bold.

Remember the trick is MySQL always adds the current timestamp during insert and update into the first timestamp column.