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

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.

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.

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.

this is indeed terrible news.[here is a long pending bug/feature request for this][1]. that discussion also talks about the limitations of timestamp data type.

I am seriously wondering what is the issue with getting this thing implemented.


[1]:

[To see links please register here]

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]

For all who use the TIMESTAMP column as a solution i want to second the following limitation from the manual:

[To see links please register here]

"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.

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;

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 ;

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!

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.