Set NOW() as Default Value for datetime datatype?
My solution
ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT
NULL 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.
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.
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
)