Sunday, October 27, 2013

time for standards 2

I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP(6)       |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6));
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(6));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---------------------+
| ts                  |
+---------------------+
| 2013-10-27 10:42:30 |
+---------------------+
1 row in set (0.00 sec)

So it didn't allow me to use a default of CURRENT_TIMESTAMP(6). It however accepted a CURRENT_TIMESTAMP with a fsp of 6, and then threw away the microseconds without any warning.
After some more investigating it turned out that there is a correct way of doing this:
mysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------------+
| ts                         |
+----------------------------+
| 2013-10-27 10:47:01.604891 |
+----------------------------+
1 row in set (0.00 sec)

So you must specify a fsp for the column time AND the default value, then it works.

No comments:

Post a Comment