UTC_TIMESTAMP()

函数说明

将当前 UTC 时间以 YYYY-MM-DD hh:mm:ss 或 YYYYMMDDhhmmss 的格式返回，返回格式取决于函数是在字符串还是数字。

函数语法

> UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

参数释义

参数	说明
fsp	可选。参数 fsp 参数用于指定分秒精度，有效值为 0 到 6 之间的整数。

示例

• 示例 1：

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| utc_timestamp()     |
+---------------------+
| 2022-09-16 03:37:40 |
+---------------------+
1 row in set (0.01 sec)

mysql> select unix_timestamp(utc_timestamp());
+---------------------------------+
| unix_timestamp(utc_timestamp()) |
+---------------------------------+
|                      1663282842 |
+---------------------------------+
1 row in set (0.02 sec)

• 示例 2：

create table t1 (ts timestamp);
set time_zone='+00:00';

mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(utc_timestamp());
+-------------------------------------------------------------------+
| unix_timestamp(utc_timestamp()) - unix_timestamp(utc_timestamp()) |
+-------------------------------------------------------------------+
|                                                                 0 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

insert into t1 (ts) values ('2003-03-30 02:30:00');
set time_zone='+10:30';

mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(utc_timestamp());
+-------------------------------------------------------------------+
| unix_timestamp(utc_timestamp()) - unix_timestamp(utc_timestamp()) |
+-------------------------------------------------------------------+
|                                                                 0 |
+-------------------------------------------------------------------+
1 row in set (0.01 sec)

insert into t1 (ts) values ('2003-03-30 02:30:00');
set time_zone='-10:00';

mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
+-----------------------------------------------------------------------+
| unix_timestamp(utc_timestamp()) - unix_timestamp(current_timestamp()) |
+-----------------------------------------------------------------------+
|                                                                 36000 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

insert into t1 (ts) values ('2003-03-30 02:30:00');

mysql> select * from t1;
+---------------------+
| ts                  |
+---------------------+
| 2003-03-29 16:30:00 |
| 2003-03-29 06:00:00 |
| 2003-03-30 02:30:00 |
+---------------------+
3 rows in set (0.00 sec)

• 示例 3：

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 select (utc_timestamp());
INSERT INTO t1 select (utc_timestamp());
INSERT INTO t1 select (utc_timestamp());

mysql> SELECT year(a) FROM t1 WHERE a > '2008-01-01';
+---------+
| year(a) |
+---------+
|    2022 |
|    2022 |
|    2022 |
+---------+
3 rows in set (0.04 sec)

DROP TABLE if exists t1;
create table t1 (a int primary key, b int, c int, d timestamp);
insert into t1 select 1,1,1,utc_timestamp();
insert into t1 select 2,0,0,null;

mysql> select a,b,c,year(d) from t1;
+------+------+------+---------+
| a    | b    | c    | year(d) |
+------+------+------+---------+
|    1 |    1 |    1 |    2022 |
|    2 |    0 |    0 |    NULL |
+------+------+------+---------+
2 rows in set (0.01 sec)

DROP TABLE if exists t1;
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 select (utc_timestamp());
INSERT INTO t1 select (utc_timestamp());

mysql> SELECT 1 FROM t1 ORDER BY 1;
+------+
| 1    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

限制

暂不支持操作符 + 或 - 对 DATETIME BIGINT 参数进行运算。