MySQL UTC_TIMESTAMP() Function

Summary: in this tutorial, you will learn how to use the MySQL UTC_TIMESTAMP() function to retrieve the current UTC date and time.

Introduction to MySQL UTC_TIMESTAMP() function

The UTC_TIMESTAMP() function returns the current coordinated universal time (UTC) date and time.

Here’s the syntax of the UTC_TIMESTAMP() function:

UTC_TIMESTAMP()Code language: SQL (Structured Query Language) (sql)

The UTC_TIMESTAMP() doesn’t require any arguments and returns the current UTC date and time.

In practice, you’ll use the UTC_TIMESTAMP() function to work with date and time values in a timezone-independent manner.

MySQL UTC_TIMESTAMP() function examples

Let’s take some examples of using the UTC_TIMESTAMP() function.

1) Simple UTC_TIMESTAMP() function example

The following example uses the UTC_TIMESTAMP() to get the current UTC time:

SELECT UTC_TIMESTAMP() AS current_utc_time;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| current_utc_time    |
+---------------------+
| 2023-10-17 06:49:00 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query will return the current UTC time depending on when you run it.

2) Using UTC_TIMESTAMP() function with table data

First, create a table called activity_logs with the following structure:

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255) NOT NULL,
    time TIMESTAMP NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the activity_logs table:

INSERT INTO activity_logs(description, time) 
VALUES
('User logged in', UTC_TIMESTAMP()),
('File uploaded', UTC_TIMESTAMP()),
('Data processed', UTC_TIMESTAMP());Code language: SQL (Structured Query Language) (sql)

The time column will have values defaulted to the current UTC date and time.

Third, query data from the activity_logs table:

SELECT * FROM activity_logs;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------+---------------------+
| id | description    | time                |
+----+----------------+---------------------+
|  1 | User logged in | 2023-10-17 07:05:19 |
|  2 | File uploaded  | 2023-10-17 07:05:19 |
|  3 | Data processed | 2023-10-17 07:05:19 |
+----+----------------+---------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In the activity_logs, we recorded the events using the UTC date and time.

Finally, query data from the activity_logs table and convert the time values of the events to US/Eastern timezone:

SELECT 
  description, 
  CONVERT_TZ(time, 'UTC', 'US/Eastern') AS time
FROM 
  activity_logs;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+---------------------+
| description    | time                |
+----------------+---------------------+
| User logged in | 2023-10-17 04:17:21 |
| File uploaded  | 2023-10-17 04:17:21 |
| Data processed | 2023-10-17 04:17:21 |
+----------------+---------------------+
3 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Note that if you see the NULL values in the time column, it’s likely that your database server is not configured with the timezone properly.

Summary

Use the MySQL UTC_TIMESTAMP() function to retrieve the current UTC date and time.

Was this tutorial helpful?