Advertisement
Advertisement


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.

2015/07/12
1
912
7/12/2015 7:15:15 PM

Accepted 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> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> 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> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

**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 "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I hope this helps. I am using 5.5.56-MariaDB **

2018/04/10
878
4/10/2018 7:15:04 PM


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.

2014/02/21

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!

2011/04/20

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]

2010/01/28

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;
2011/02/12

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

2016/05/26

Source: https://stackoverflow.com/questions/168736
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]