Set NOW() as Default Value for datetime datatype?

Question

As of MySQL 5.6.5, you can use the DATETIME type with a dynamic default value:

CREATE TABLE foo (
    creation_time      DATETIME DEFAULT   CURRENT_TIMESTAMP,
    modification_time  DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Or even combine both rules:

modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Prior to 5.6.5, you need to use the TIMESTAMP data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP field can exist per table.

CREATE TABLE mytable (
  mydate TIMESTAMP
)

See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

If you want to prevent MySQL from updating the timestamp value on UPDATE (so that it only triggers on INSERT) you can change the definition to:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

I use a trigger as a workaround to set a datetime field to NOW() for new inserts:

CREATE TRIGGER `triggername` BEFORE INSERT ON  `tablename` 
FOR EACH ROW 
SET NEW.datetimefield = NOW()

it should work for updates too

Answers by Johan & Leonardo involve converting to a timestamp field. Although this is probably ok for the use case presented in the question (storing RegisterDate and LastVisitDate), it is not a universal solution. See datetime vs timestamp question.

My solution

ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT
NULL DEFAULT CURRENT_TIMESTAMP;

EUREKA !!!

---

For all those who lost heart trying to set a default DATETIME value in MySQL, I know exactly how you feel/felt. So here it is:

`ALTER TABLE  `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0

Carefully observe that I haven't added single quotes/double quotes around the 0.

---

Important update:

This answer was posted long back. Back then, it worked on my (probably latest) installation of MySQL and I felt like sharing it. Please read the comments below before you decide to use this solution now.

On versions mysql 5.6.5 and newer, you can use precise datetimes and set default values as well. There is a subtle bit though, which is to pass in the precision value to both the datetime and the NOW() function call.

This Example Works:

    ALTER TABLE my_table MODIFY created datetime(6) NOT NULL DEFAULT NOW(6);

This Example Does not Work:

    ALTER TABLE my_table MODIFY created datetime(6) NOT NULL DEFAULT NOW();

mysql 5.6 docs say that CURRENT_TIMESTAMP can be used as default for both TIMESTAMP and DATETIME data types:

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