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

MySQL 5.6 has fixed this problem.

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'

Answer 2

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.

Answer 3

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!

Answer 4

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 ;

Answer 5

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

Answer 6

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').