UTC_TIMESTAMP()

Function Description

Returns the current UTC time in the format of YYYY-MM-DD hh:mm:ss or YYYMMDDhhmmss, depending on whether the function is in a string or a number.

Function Syntax

> UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

Parameter definition

Parameters	Description
fsp	Optional. Parameter fsp parameter is used to specify the minute and second accuracy, with valid values ​​of integers between 0 and 6.

Example

• Example 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)

• Example 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)

• Example 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)

limit

Operators + or - are not supported for the operation of the DATETIME BIGINT parameter.