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

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

---

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.

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;

---

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

---

MySQL 5.6 has fixed this problem.

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

---

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.

---

While you can't do this with DATETIME in the default definition, you can simply incorporate a select statement in your insert statement like this:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

Note the lack of quotes around the table.

For MySQL 5.5

---

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.

---

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

---

Use the following code

DELIMITER $$

    CREATE TRIGGER bu_table1_each BEFORE UPDATE ON table1 FOR EACH ROW
    BEGIN
      SET new.datefield = NOW();
    END $$

    DELIMITER ;

---

Take for instance If I had a table named 'site' with a created_at and an update_at column that were both DATETIME and need the default value of now, I could execute the following sql to achieve this.

ALTER TABLE `site` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `created_at` `created_at` DATETIME  NULL DEFAULT NULL;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` DATETIME NULL DEFAULT  NULL;

The sequence of statements is important because a table can not have two columns of type TIMESTAMP with default values of CUREENT TIMESTAMP

---

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.