Mysql+Demo 获取当前时间(不含日期)的方式-优快云博客

本文链接：https://blog.youkuaiyun.com/myyhtw/article/details/146763528

记录一下mysql获取当前时间(不含日期)的方式

格式

%H:%i:%s

%H:%i:%s.%f

方式一：CURTIME() 高版本mysql支持精度，可支持到微秒级别

SELECT CURTIME();
SELECT CURTIME(6);

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:49:01  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(1);
+------------+
| CURTIME(1) |
+------------+
| 13:49:03.6 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(2);
+-------------+
| CURTIME(2)  |
+-------------+
| 13:49:05.75 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(3);
+--------------+
| CURTIME(3)   |
+--------------+
| 13:49:08.144 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(4);
+---------------+
| CURTIME(4)    |
+---------------+
| 13:49:10.4727 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(5);
+----------------+
| CURTIME(5)     |
+----------------+
| 13:49:12.62294 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(6);
+-----------------+
| CURTIME(6)      |
+-----------------+
| 13:49:16.095559 |
+-----------------+
1 row in set (0.00 sec)

mysql>

方式二：current_time系统变量

select current_time;

mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 13:51:01     |
+--------------+
1 row in set (0.00 sec)

mysql>

方式三：current_time()函数，高版本支持精度，可支持到微秒级别

select current_time();
select current_time(3);
select current_time(6);

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 13:52:41       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time(3);
+-----------------+
| current_time(3) |
+-----------------+
| 13:53:30.594    |
+-----------------+
1 row in set (0.00 sec)

mysql> select current_time(6);
+-----------------+
| current_time(6) |
+-----------------+
| 13:53:35.338065 |
+-----------------+
1 row in set (0.00 sec)

mysql>

方式四：time(x)函数，time(now())和time(current_timestamp())

select time(now());
select time(now(3));
select time(now(6));

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 13:59:46    |
+-------------+
1 row in set (0.00 sec)

mysql> select time(now(3));
+--------------+
| time(now(3)) |
+--------------+
| 13:59:50.418 |
+--------------+
1 row in set (0.00 sec)

mysql> select time(now(6));
+-----------------+
| time(now(6))    |
+-----------------+
| 13:59:55.032717 |
+-----------------+
1 row in set (0.00 sec)

mysql>

select time(current_timestamp());
select time(current_timestamp(3));
select time(current_timestamp(6));

mysql> select time(current_timestamp());
+---------------------------+
| time(current_timestamp()) |
+---------------------------+
| 14:01:57                  |
+---------------------------+
1 row in set (0.01 sec)

mysql> select time(current_timestamp(3));
+----------------------------+
| time(current_timestamp(3)) |
+----------------------------+
| 14:02:00.669               |
+----------------------------+
1 row in set (0.00 sec)

mysql> select time(current_timestamp(6));
+----------------------------+
| time(current_timestamp(6)) |
+----------------------------+
| 14:02:03.566709            |
+----------------------------+
1 row in set (0.00 sec)

mysql>

同一条查询语句中，执行三次查询，查看它们的微秒级别会不会有所变化

mysql> SELECT CURTIME(6),CURTIME(6),CURTIME(6);
+-----------------+-----------------+-----------------+
| CURTIME(6)      | CURTIME(6)      | CURTIME(6)      |
+-----------------+-----------------+-----------------+
| 14:03:59.050793 | 14:03:59.050793 | 14:03:59.050793 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql>

mysql> select current_time(6),current_time(6),current_time(6);
+-----------------+-----------------+-----------------+
| current_time(6) | current_time(6) | current_time(6) |
+-----------------+-----------------+-----------------+
| 14:04:34.916893 | 14:04:34.916893 | 14:04:34.916893 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql>

mysql> select time(current_timestamp(6)),time(current_timestamp(6)),time(current_timestamp(6));
+----------------------------+----------------------------+----------------------------+
| time(current_timestamp(6)) | time(current_timestamp(6)) | time(current_timestamp(6)) |
+----------------------------+----------------------------+----------------------------+
| 14:05:43.288391            | 14:05:43.288391            | 14:05:43.288391            |
+----------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> select time(now(6)),time(now(6)),time(now(6));
+-----------------+-----------------+-----------------+
| time(now(6))    | time(now(6))    | time(now(6))    |
+-----------------+-----------------+-----------------+
| 14:06:06.959816 | 14:06:06.959816 | 14:06:06.959816 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
mysql>