How do you set a default value for a MySQL Datetim

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.

Answer 1

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]

Answer 2

Use the following code

DELIMITER $$

    CREATE TRIGGER bu_table1_each BEFORE UPDATE ON table1 FOR EACH ROW
    BEGIN
      SET new.datefield = NOW();
    END $$

    DELIMITER ;

Answer 3

If you have already created the table then you can use

To change default value to current date time

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

To change default value to '2015-05-11 13:01:01'

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT '2015-05-11 13:01:01';

Answer 4

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

Answer 5

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;

Answer 6

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