UTC_TIMESTAMP()

函数说明

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

函数语法

1. > UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

参数释义

示例

• 示例 1：

1. mysql> SELECT UTC_TIMESTAMP();
2. +---------------------+
3. | utc_timestamp()     |
4. +---------------------+
5. | 2022-09-16 03:37:40 |
6. +---------------------+
7. 1 row in set (0.01 sec)

9. mysql> select unix_timestamp(utc_timestamp());
10. +---------------------------------+
11. | unix_timestamp(utc_timestamp()) |
12. +---------------------------------+
13. |                      1663282842 |
14. +---------------------------------+
15. 1 row in set (0.02 sec)

• 示例 2：

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

4. mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(utc_timestamp());
5. +-------------------------------------------------------------------+
6. | unix_timestamp(utc_timestamp()) - unix_timestamp(utc_timestamp()) |
7. +-------------------------------------------------------------------+
8. |                                                                 0 |
9. +-------------------------------------------------------------------+
10. 1 row in set (0.00 sec)

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

15. mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(utc_timestamp());
16. +-------------------------------------------------------------------+
17. | unix_timestamp(utc_timestamp()) - unix_timestamp(utc_timestamp()) |
18. +-------------------------------------------------------------------+
19. |                                                                 0 |
20. +-------------------------------------------------------------------+
21. 1 row in set (0.01 sec)

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

26. mysql> select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp());
27. +-----------------------------------------------------------------------+
28. | unix_timestamp(utc_timestamp()) - unix_timestamp(current_timestamp()) |
29. +-----------------------------------------------------------------------+
30. |                                                                 36000 |
31. +-----------------------------------------------------------------------+
32. 1 row in set (0.00 sec)

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

36. mysql> select * from t1;
37. +---------------------+
38. | ts                  |
39. +---------------------+
40. | 2003-03-29 16:30:00 |
41. | 2003-03-29 06:00:00 |
42. | 2003-03-30 02:30:00 |
43. +---------------------+
44. 3 rows in set (0.00 sec)

• 示例 3：

1. DROP TABLE IF EXISTS t1;
2. CREATE TABLE t1 (a TIMESTAMP);
3. INSERT INTO t1 select (utc_timestamp());
4. INSERT INTO t1 select (utc_timestamp());
5. INSERT INTO t1 select (utc_timestamp());

7. mysql> SELECT year(a) FROM t1 WHERE a > '2008-01-01';
8. +---------+
9. | year(a) |
10. +---------+
11. |    2022 |
12. |    2022 |
13. |    2022 |
14. +---------+
15. 3 rows in set (0.04 sec)

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

6. mysql> select a,b,c,year(d) from t1;
7. +------+------+------+---------+
8. | a    | b    | c    | year(d) |
9. +------+------+------+---------+
10. |    1 |    1 |    1 |    2022 |
11. |    2 |    0 |    0 |    NULL |
12. +------+------+------+---------+
13. 2 rows in set (0.01 sec)

1. DROP TABLE if exists t1;
2. CREATE TABLE t1 (a TIMESTAMP);
3. INSERT INTO t1 select (utc_timestamp());
4. INSERT INTO t1 select (utc_timestamp());

6. mysql> SELECT 1 FROM t1 ORDER BY 1;
7. +------+
8. | 1    |
9. +------+
10. |    1 |
11. |    1 |
12. +------+
13. 2 rows in set (0.01 sec)

限制

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