How do you set a default value for a MySQL Datetime column

Question

How do you set a default value for a MySQL Datetime column   In SQL Server it s getdate    What is the equivalant for MySQL  I m using MySQL 5 x if that is a factor

User · Answer

I think it simple in mysql since mysql the inbuilt function called now() which gives current time(time of that insert).

So your query should look like similarly

CREATE TABLE defaultforTime(
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME default now()
);

Thank you.

User · Answer

CREATE TABLE  testtable         id  INT 10  NULL DEFAULT NULL       colname  DATETIME NULL DEFAULT  1999-12-12 12 12 12      In the above query to create  testtable   i used  1999-12-12 12 12 12  as default value for DATETIME column colname

User · Answer

this is indeed terrible news here is a long pending bug feature request for this  that discussion also talks about the limitations of timestamp data type   I am seriously wondering what is the issue with getting this thing implemented

User · Answer

Take for instance If I had a table named  site  with a created at and an update at column that were both DATETIME and need the default value of now  I could execute the following sql to achieve this    ALTER TABLE  site  CHANGE  created at   created at  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP   ALTER TABLE  site  CHANGE  created at   created at  DATETIME  NULL DEFAULT NULL   ALTER TABLE  site  CHANGE  updated at   updated at  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP   ALTER TABLE  site  CHANGE  updated at   updated at  DATETIME NULL DEFAULT  NULL    The sequence of statements is important because a table can not have two columns of type TIMESTAMP with default values of CUREENT TIMESTAMP

User · Answer

If you are trying to set default value as NOW   MySQL supports that you have to change the type of that column TIMESTAMP instead of DATETIME  TIMESTAMP  have current date and time as default  i think it will resolved your problem

User · Answer

MySQL 5 6 has fixed this problem   ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT  CURRENT TIMESTAMP

User · Answer

For all who use the TIMESTAMP column as a solution i want to second the following limitation from the manual   http   dev mysql com doc refman 5 0 en datetime html   The TIMESTAMP data type has a range of  1970-01-01 00 00 01  UTC to  2038-01-19 03 14 07  UTC  It has varying properties  depending on the MySQL version and the SQL mode the server is running in  These properties are described later in this section     So this will obviously break your software in about 28 years   I believe the only solution on the database side is to use triggers like mentioned in other answers

User · Answer

IMPORTANT EDIT  It is now possible to achieve this with DATETIME fields since MySQL 5 6 5  take a look at the other post below    Previous versions can t do that with DATETIME    But you can do it with TIMESTAMP  mysql gt  create table test  str varchar 32   ts TIMESTAMP DEFAULT CURRENT TIMESTAMP   Query OK  0 rows affected  0 00 sec   mysql gt  desc test   ------- ------------- ------ ----- ------------------- -------    Field   Type          Null   Key   Default             Extra    ------- ------------- ------ ----- ------------------- -------    str     varchar 32    YES          NULL                           ts      timestamp     NO           CURRENT TIMESTAMP             ------- ------------- ------ ----- ------------------- -------  2 rows in set  0 00 sec   mysql gt  insert into test  str  values   quot demo quot    Query OK  1 row affected  0 00 sec   mysql gt  select   from test   ------ ---------------------    str    ts                     ------ ---------------------    demo   2008-10-03 22 59 52     ------ ---------------------  1 row in set  0 00 sec   mysql gt   CAVEAT  IF you define a column with CURRENT TIMESTAMP ON as default  you will need to ALWAYS specify a value for this column or the value will automatically reset itself to  quot now   quot  on update  This means that if you do not want the value to change  your UPDATE statement must contain  quot  your column name     your column name  quot   or some other value  or the value will become  quot now   quot   Weird  but true  I hope this helps  I am using 5 5 56-MariaDB

User · Answer

This is my trigger example    x000D   x000D                ROLE               x000D  drop table if exists  role   x000D  create table  role    x000D       id role  bigint 20  unsigned not null auto increment  x000D       date created  datetime  x000D       date deleted  datetime  x000D       name  varchar 35  not null  x000D       description  text  x000D      primary key   id role   x000D    comment     x000D   x000D  drop trigger if exists  role date created   x000D  create trigger  role date created  before insert x000D      on  role  x000D      for each row  x000D      set new  date created    now    x000D   x000D   x000D

User · Answer

You can resolve the default timestamp  First consider which character set you are using for example if u taken utf8 this character set support all languages and if u taken laten1 this character set support only for English  Next setp if you are working under any project you should know client time zone and select you are client zone  This step are mandatory

User · Answer

I m running MySql Server 5 7 11 and this sentence   ALTER TABLE table name CHANGE date column datetime NOT NULL DEFAULT  0000-00-00 00 00 00    is not working  But the following    ALTER TABLE table name CHANGE date column datetime NOT NULL DEFAULT  1000-01-01 00 00 00    just works     As a sidenote  it is mentioned in the mysql docs        The DATE type is used for values with a date part but no time part  MySQL retrieves and displays DATE values in  YYYY-MM-DD  format  The supported range is  1000-01-01  to  9999-12-31     even if they also say      Invalid DATE  DATETIME  or TIMESTAMP values are converted to the    zero    value of the appropriate type   0000-00-00  or  0000-00-00 00 00 00

User · Answer

Here is how to do it on MySQL 5 1   ALTER TABLE  table name  CHANGE  column name   column name   TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP    I have no clue why you have to enter the column name twice

User · Answer

Use the following code      DELIMITER         CREATE TRIGGER bu table1 each BEFORE UPDATE ON table1 FOR EACH ROW     BEGIN       SET new datefield   NOW        END         DELIMITER

User · Answer

While defining multi-line triggers one has to change the delimiter as semicolon will be taken by MySQL compiler as end of trigger and generate error  e g   DELIMITER    CREATE TRIGGER  MyTable UPDATE  BEFORE UPDATE ON  MyTable  FOR EACH ROW BEGIN         -- Set the udpate date     Set new UpdateDate   now    END   DELIMITER

User · Answer

If you have already created the table then you can use   To change default value to current date time  ALTER TABLE  lt TABLE NAME gt   CHANGE COLUMN  lt COLUMN NAME gt   lt COLUMN NAME gt  DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP    To change default value to  2015-05-11 13 01 01   ALTER TABLE  lt TABLE NAME gt   CHANGE COLUMN  lt COLUMN NAME gt   lt COLUMN NAME gt  DATETIME NOT NULL DEFAULT  2015-05-11 13 01 01

User · Answer

While you can t do this with DATETIME in the default definition  you can simply incorporate a select statement in your insert statement like this   INSERT INTO Yourtable  Field1  YourDateField  VALUES  val1    select now       Note the lack of quotes around the table   For MySQL 5 5

User · Answer

In version 5 6 5  it is possible to set a default value on a datetime column  and even make a column that will update when the row is updated  The type definition   CREATE TABLE foo        creation time      DATETIME DEFAULT CURRENT TIMESTAMP       modification time  DATETIME ON UPDATE CURRENT TIMESTAMP     Reference  http   optimize-this blogspot com 2012 04 datetime-default-now-finally-available html

User · Answer

For all those who lost heart trying to set a default DATETIME value in MySQL  I know exactly how you feel felt  So here is 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  I m literally jumping after solving this one  D

User · Answer

If you are trying to set default value as NOW    I don t think MySQL supports that  In MySQL  you cannot use a function or an expression as the default value for any type of column  except for the TIMESTAMP data type column  for which you can specify the CURRENT TIMESTAMP as the default

User · Answer

MySQL  before version 5 6 5  does not allow functions to be used for default DateTime values   TIMESTAMP is not suitable due to its odd behavior and is not recommended for use as input data   See MySQL Data Type Defaults    That said  you can accomplish this by creating a Trigger   I have a table with a DateCreated field of type DateTime   I created a trigger on that table  Before Insert  and  SET NEW DateCreated NOW    and it works great   I hope this helps somebody

User · Answer

For me the trigger approach has worked the best  but I found a snag with the approach   Consider the basic trigger to set a date field to the current time on insert   CREATE TRIGGER myTable OnInsert BEFORE INSERT ON  tblMyTable      FOR EACH ROW SET NEW dateAdded   NOW      This is usually great  but say you want to set the field manually via INSERT statement  like so   INSERT INTO tblMyTable name  dateAdded  VALUES  Alice    2010-01-03 04 30 43      What happens is that the trigger immediately overwrites your provided value for the field  and so the only way to set a non-current time is a follow up UPDATE statement--yuck   To override this behavior when a value is provided  try this slightly modified trigger with the IFNULL operator   CREATE TRIGGER myTable OnInsert BEFORE INSERT ON  tblMyTable      FOR EACH ROW SET NEW dateAdded   IFNULL NEW dateAdded  NOW       This gives the best of both worlds  you can provide a value for your date column and it will take  and otherwise it ll default to the current time   It s still ghetto relative to something clean like DEFAULT GETDATE   in the table definition  but we re getting closer

User · Answer

I was able to solve this using this alter statement on my table that had two datetime fields   ALTER TABLE  test table    CHANGE COLUMN  created dt   created dt  TIMESTAMP NOT NULL DEFAULT  0000-00-00 00 00 00     CHANGE COLUMN  updated dt   updated dt  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP    This works as you would expect the now   function to work  Inserting nulls or ignoring the created dt and updated dt fields results in a perfect timestamp value in both fields  Any update to the row changes the updated dt  If you insert records via the MySQL query browser you needed one more step  a trigger to handle the created dt with a new timestamp   CREATE TRIGGER trig test table insert BEFORE INSERT ON  test table      FOR EACH ROW SET NEW created dt   NOW      The trigger can be whatever you want I just like the naming convention  trig   my table name   insert

User · Answer

You can use triggers to do this type of stuff   CREATE TABLE  MyTable     MyTable ID   int UNSIGNED NOT NULL AUTO INCREMENT    MyData   varchar 10  NOT NULL    CreationDate   datetime NULL    UpdateDate   datetime NULL   PRIMARY KEY   MyTable ID        CREATE TRIGGER  MyTable INSERT  BEFORE INSERT ON  MyTable  FOR EACH ROW BEGIN         -- Set the creation date     SET new CreationDate   now             -- Set the udpate date     Set new UpdateDate   now    END   CREATE TRIGGER  MyTable UPDATE  BEFORE UPDATE ON  MyTable  FOR EACH ROW BEGIN         -- Set the udpate date     Set new UpdateDate   now    END

User · Answer

Working fine with MySQL 8 x      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

If  you set ON UPDATE CURRENT TIMESTAMP it will take current time when row data update in table   CREATE TABLE bar           create time  TIMESTAMP CURRENT TIMESTAMP           update time  TIMESTAMP ON UPDATE CURRENT TIMESTAMP

User · Answer

You can use now   to set the value of a datetime column  but keep in mind that you can t use that as a default value

[mysql] How do you set a default value for a MySQL Datetime column?

Examples related to mysql

Examples related to datetime