Set NOW as Default Value for datetime datatype

Question

I have two columns in table users namely registerDate and lastVisitDate which consist of datetime data type  I would like to do the following    Set registerDate defaults value to MySQL NOW   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

User · Answer

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

User · Answer

This worked for me  using MySQL   ALTER TABLE  table name  MODIFY  column name  datetime NOT NULL DEFAULT NOW

User · Answer

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  amp  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

User · Answer

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

User · Answer

This worked for me - just changed INSERT to UPDATE for my table   INSERT INTO Yourtable  Field1  YourDateField  VALUES  val1    select now

User · Answer

My solution  ALTER TABLE  table name  MODIFY COLUMN  column name  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP

User · Answer

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

User · Answer

ALTER TABLE table name   CHANGE COLUMN date column name date column name DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP    Finally  This worked for me

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Set NOW() as Default Value for datetime datatype?

EUREKA !!!

Examples related to mysql

Examples related to sql

Examples related to datetime

Examples related to mysql-error-1067