value - mysql not null default




Set NOW() as Default Value for datetime datatype? (8)

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?


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
)

My solution

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

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 best way is make a trigger:

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

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

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


ALTER TABLE table_name
  CHANGE COLUMN date_column_name date_column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Finally, This worked for me!





mysql-error-1067