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

CREATE TABLE `testtable` (
    `id` INT(10) NULL DEFAULT NULL,
    `colname` DATETIME NULL DEFAULT '1999-12-12 12:12:12'
)

In the above query to create 'testtable', i used '1999-12-12 12:12:12' as default value for DATETIME column colname

Answer 2

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 3

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 4

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.

Answer 5

If you are trying to set default value as NOW(),MySQL supports that you have to change the type of that column TIMESTAMP instead of DATETIME. TIMESTAMP have current date and time as default..i think it will resolved your problem..

Answer 6

Here is how to do it on MySQL 5.1:

ALTER TABLE `table_name` CHANGE `column_name` `column_name` 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

I have no clue why you have to enter the column name twice.