Mysql – I thought curtime(), now() and current_timestamp are valid default datetime values in MySql

datetimedefault-valueMySQL

I'm using the latest version of MySql and I tried altering a table to use curtime() or now() or current_timestamp as the default value for a datetime column (created_at, updated_at). For all three, the alter failed, saying these are invalid default values. Any ideas what's an appropriate default value to generate the current time in a datetime field?

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT current_timestamp,
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT current_timestamp;

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT now(),
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT now();

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT curtime(),
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT curtime();

Best Answer

Try using the TIMESTAMP column type:

MODIFY COLUMN `updated_at` TIMESTAMP NOT NULL DEFAULT current_timestamp;

Related Solutions

Mysql – How to 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 am using 5.5.56-MariaDB

Mysql – Set NOW() as Default Value for datetime datatype

As of MySQL 5.6.5, you can use the DATETIME type with a dynamic default value:

CREATE TABLE foo (
    creation_time      DATETIME DEFAULT   CURRENT_TIMESTAMP,
    modification_time  DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Or even combine both rules:

modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Prior to 5.6.5, you need to use the TIMESTAMP data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP field can exist per table.

CREATE TABLE mytable (
  mydate TIMESTAMP
)

See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

If you want to prevent MySQL from updating the timestamp value on UPDATE (so that it only triggers on INSERT) you can change the definition to:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

Best Answer

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

Mysql – Set NOW() as Default Value for datetime datatype

Related Topic