Inserting NULL into MySQL timestamp

I’m observing unexpected behavior with inserting/updating NULL in a MySQL timestamp column.

Consider the following statements.

create table temp(id int, hireDate timestamp default 0);
insert into temp (id) values(1);
insert into temp (id, hireDate) values(2, null);
select * from temp;
id  hireDate
-----------------------------
1   
2   2012-09-19 10:54:10.0

The first insert (when hiredate is not specified in the SQL, hireDate is null(0) which is expected.

However when an explicit null passed in SQL, the current date time is inserted which is unexpected. Why does this happen?

Note: Hibernate uses second type of insert and hence it become an issue. How do I insert null into a timestamp column?

Answer

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

In order to allow a TIMESTAMP to be nullable, create it using the NULL attribute, or alter the table and add the NULL attribute. In a create statement, it would resemble.

CREATE TABLE t1 (tsvalue TIMESTAMP NULL, ... );

Inserting a NULL value into a TIMESTAMP field with the NULL attribute set will result in the field being set to NULL instead of to NOW().