Inserting NULL into MySQL timestamp

Question

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 1

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().

Answer 2

You can insert and update NULL into a MySQL timestamp.

Create the table:

create table penguins(id int primary key auto_increment, the_time timestamp NULL); 

Insert some rows:

insert into penguins(the_time) values (now()); insert into penguins(the_time) values (null); insert into penguins(the_time) values (0); insert into penguins(the_time) values ('1999-10-10 01:02:03'); 

Which prints:

select * from penguins  1   2015-01-23 15:40:36 2    3   0000-00-00 00:00:00 4   1999-10-10 01:02:03 

The column called the_time with datatype: timestamp in 2nd row has the value NULL.