显示当前时间:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| -- :: |
+---------------------+
row in set (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| -- :: |
+---------------------+
row in set (0.00 sec)
以秒形式显示当前时间:
mysql> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| |
+------------------+
row in set (0.00 sec)
精确到毫秒:
mysql> SELECT ROUND(UNIX_TIMESTAMP(CURTIME()) * );
+------------------------------------------+
| ROUND(UNIX_TIMESTAMP(CURTIME()) * ) |
+------------------------------------------+
| |
+------------------------------------------+
row in set (0.02 sec)
mysql在5.6.4版本之前会将秒之后的时间截取掉,也就是最高精度只到秒。5.6.4及之后的版本支持到微秒(2010-12-10 14:12:09.019473,小数点后6位)。不过创建表时需要指定精度,指定小数点后存储几位:
CREATE TABLE t1 (t TIME(), dt DATETIME());
例如:
mysql> CREATE TABLE fractest( c1 TIME(), c2 DATETIME(), c3 TIMESTAMP() );
Query OK, rows affected (0.61 sec) mysql> INSERT INTO fractest VALUES ('17:51:04.777', '2016-10-10 17:51:04.777', '2016-10-10 17:51:04.777');
Query OK, row affected (0.05 sec) mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1 | c2 | c3 |
+-------------+------------------------+------------------------+
| ::04.78 | -- ::04.78 | -- ::04.78 |
+-------------+------------------------+------------------------+
row in set (0.00 sec)