UTC_TIMESTAMP()

Description

Returns the current UTC date and time as a value in YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context.

Syntax

  1. > UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

Arguments

ArgumentsDescription
fspOptional. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

Examples

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

Constraints

Operator + or - with parameters DATETIME BIGINT is not supported now.