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 5.x if that is a factor.

Answer 1

NOW()

MySQL :: MySQL 5.0 Reference Manual :: 11.6 Date and Time Functions

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

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.

Answer 4

You 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>

Answer 5

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.

Answer 6

alter table add DateTime default getdate()

Answer 7

MySQL does not allow functions to be used for default DateTime values. TIMESTAMP is not suitable due to it's odd behavior and is not recommended for use as input data. (http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html)

That said, you can accomplish this by creating a "Trigger" (samepath/trigger-syntax.html).

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.

Answer 8

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 9

MySQL 5.1.37-1ubuntu here.

CREATE TABLE `t1` (
  `updated_at` TIMESTAMP,
  `data` CHAR(255) DEFAULT
);

On record creation updated_at='0000-00-00' and on each update it changes to current datetime.

So I don't really care what documentation says, TIMESTAMP changes on each record update.

Answer 10

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.

Answer 11

You can reslove the defaul 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 charcter 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 mendatory.