CODEWITHSUNDEEP
mysqldatedatetime
Mike
Mike

Reputation: 375

Behavior difference between TIMESTAMP and DATE

I have a table that has two TIMESTAMP columns (Creation_Date and Edit_date) that auto fill on INSERT (both columns get CURRENT_TIMESTAMP) and UPDATE (only the Edit_date column is changed)

The action is done by this code:

... CreationDate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', EditDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...

I need to change the type from TIMESTAMP to DATE and have tried:

...
`CREATED_DATE` date NOT NULL DEFAULT '0000-00-00',
`EDITED_DATE` date NOT NULL DEFAULT CURRENT_DATE ON UPDATE CURRENT_DATE,
...

How can I get the same behavior. Any responses would be appreciated.

Upvotes: 1

Views: 130

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

No its not possible unless you are using mysql 5.6.5 The only option was available until 5.6.5 is to use field type as timestamp and then set a default which is not constant.

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP

If you are using mysql 5.6.5 then you can set this behaviour for DATETIME datatype

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

Upvotes: 1

Related Questions