# mysql – Set NOW() as Default Value for datetime datatype?

## The Question :

203 people think this question is useful

I have two columns in table users namely registerDate and lastVisitDate which consist of datetime data type. I would like to do the following.

1. Set registerDate defaults value to MySQL NOW()
2. Set lastVisitDate default value to 0000-00-00 00:00:00 Instead of null which it uses by default.

Because the table already exists and has existing records, I would like to use Modify table. I’ve tried using the two piece of code below, but neither works.

ALTER TABLE users MODIFY registerDate datetime DEFAULT NOW()
ALTER TABLE users MODIFY registerDate datetime DEFAULT CURRENT_TIMESTAMP;



It gives me Error : ERROR 1067 (42000): Invalid default value for 'registerDate'

Is it possible for me to set the default datetime value to NOW() in MySQL?

The Question Comments :
• ALTER TABLE users MODIFY dateTime timestamp default CURRENT_TIMESTAMP. You did not defined the data type of field in both of your efforts
• Since MySQL 5.6 DATE should work with default value NOW()
• According to this mysql 5.6 documentation: dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html, you can use CURRENT_TIMESTAMP() as default.
• how would i implement CURRENT_TIMESTAMP + 1 DAY

## The Answer 1

250 people think this answer is useful

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



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
)



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
)



## The Answer 2

71 people think this answer is useful

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.

## The Answer 3

46 people think this answer is useful

My solution

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



## The Answer 4

13 people think this answer is useful

## 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.

## The Answer 5

6 people think this answer is useful

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



## The Answer 6

4 people think this answer is useful

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

## The Answer 7

1 people think this answer is useful
ALTER TABLE  table_name CHANGE column_name
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP



Can be used to update the timestamp on update.

## The Answer 8

1 people think this answer is useful

The best way is using “DEFAULT 0”. Other way:

    /************ ROLE ************/
drop table if exists role;
create table role (
id_role bigint(20) unsigned not null auto_increment,
date_created datetime,
date_deleted datetime,
name varchar(35) not null,
description text,
primary key (id_role)
) comment='';

drop trigger if exists role_date_created;
create trigger role_date_created before insert
on role
for each row
set new.date_created = now();



## The Answer 9

0 people think this answer is useful

This worked for me, using MySQL:

ALTER TABLE table_name MODIFY column_name datetime NOT NULL DEFAULT NOW();



## The Answer 10

0 people think this answer is useful
ALTER TABLE table_name
CHANGE COLUMN date_column_name date_column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;



Finally, This worked for me!

## The Answer 11

0 people think this answer is useful
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
dateCreated datetime DEFAULT CURRENT_TIMESTAMP,
dateUpdated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY mobile_UNIQUE (mobile)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



## The Answer 12

-1 people think this answer is useful

Not sure if this is still active but here goes.

Regarding setting the defaults to Now(), I don’t see that to be possible for the DATETIME data type. If you want to use that data type, set the date when you perform the insert like this:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))



My version of mySQL is 5.5

## The Answer 13

-1 people think this answer is useful

This worked for me – just changed INSERT to UPDATE for my table.

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))