MySQL - default value for TIMESTAMP(3)

Question

My database is MySql 5.6.

I want to use CURRENT_TIMESTAMP as the default value an attribute which is type of TIMESTAMP(3).

But I get the error:

ERROR 1067 (42000): Invalid default value for 'updated'

I think it is because CURRENT_TIMESTAMP is only in precision of second.

How can I set current time as the default value for a timestamp with fractional part?

Try first by not having 3 in TIMESTAMP(3). Also make sure that you only have one field in the table which is using CURRENT_TIMESTAMP. — Ozair Kafray

Ravinder Reddy Ravinder Reddy · Accepted Answer · 2014-05-15T07:14:49

As per documentation on timestamp and datetime type columns:

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition.

This is permitted:
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

Other Examples:

mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now() );
ERROR 1067 (42000): Invalid default value for 'ts'

mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now(3) );
Query OK, 0 rows affected (0.59 sec)

mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp );
ERROR 1067 (42000): Invalid default value for 'ts'

mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp(3) );
Query OK, 0 rows affected (0.38 sec)

mysql> desc tbl_so_q23671222_1;
+-------+--------------+------+-----+----------------------+-------+
| Field | Type         | Null | Key | Default              | Extra |
+-------+--------------+------+-----+----------------------+-------+
| ts    | timestamp(3) | NO   |     | CURRENT_TIMESTAMP(3) |       |
+-------+--------------+------+-----+----------------------+-------+
1 row in set (0.01 sec)

mysql> desc tbl_so_q23671222_2;
+-------+--------------+------+-----+----------------------+-------+
| Field | Type         | Null | Key | Default              | Extra |
+-------+--------------+------+-----+----------------------+-------+
| ts    | timestamp(3) | NO   |     | CURRENT_TIMESTAMP(3) |       |
+-------+--------------+------+-----+----------------------+-------+
1 row in set (0.01 sec)

Refer to:
Initialization and Updating for TIMESTAMP and DATETIME

MySQL - default value for TIMESTAMP(3)

1 Answers