How the UTC_TIMESTAMP() function works in Mariadb?

The UTC_TIMESTAMP() function in MariaDB returns the current date and time in the Coordinated Universal Time (UTC) time zone. It is useful for generating timestamps that are independent of the server’s local time zone, which is essential for applications that need to work with consistent timestamps across different systems or locations.

Syntax

The syntax for the MariaDB UTC_TIMESTAMP() function is as follows:

UTC_TIMESTAMP([precision])

• precision (optional): An integer value that specifies the number of fractional digits to include in the returned timestamp. If omitted, the function returns the timestamp without fractional digits.

The function returns the current UTC timestamp in the following format:

• In a string context (e.g., when used with string functions), the timestamp is returned as a string in the format ‘YYYY-MM-DD HH:MM:SS’.
• In a numeric context (e.g., when used with numeric functions or stored in a numeric column), the timestamp is returned as a number in the format YYYYMMDDHHMMSS.uuuuuu, where the fractional part (uuuuuu) depends on the specified precision.

Examples

Example 1: Get the current UTC timestamp as a string

This example demonstrates how to use the UTC_TIMESTAMP() function to retrieve the current UTC timestamp as a string.

SELECT UTC_TIMESTAMP();

The following is the output:

+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2024-03-10 09:12:07 |
+---------------------+

This output represents the current UTC date and time in the ‘YYYY-MM-DD HH:MM:SS’ format.

Example 2: Get the current UTC timestamp with microsecond precision

This example shows how to use the UTC_TIMESTAMP() function with a specified precision to include microseconds in the returned timestamp.

SELECT UTC_TIMESTAMP(6);

The following is the output:

+----------------------------+
| UTC_TIMESTAMP(6)           |
+----------------------------+
| 2024-03-10 09:12:20.254071 |
+----------------------------+

This output represents the current UTC timestamp, including six fractional digits for microsecond precision.

Example 3: Store UTC timestamp in a table

This example demonstrates how to store the UTC timestamp in a table using the UTC_TIMESTAMP() function.

DROP TABLE IF EXISTS timestamps;
CREATE TABLE timestamps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp_str VARCHAR(19),
  timestamp_num BIGINT
);

INSERT INTO timestamps (timestamp_str, timestamp_num)
VALUES (UTC_TIMESTAMP(), UTC_TIMESTAMP(6));

SELECT * FROM timestamps;

The following is the output:

+----+---------------------+----------------+
| id | timestamp_str       | timestamp_num  |
+----+---------------------+----------------+
|  1 | 2024-03-10 09:13:39 | 20240310091339 |
+----+---------------------+----------------+

This example creates a table timestamps with columns for storing the UTC timestamp as a string (timestamp_str) and as a numeric value (timestamp_num). It then inserts the current UTC timestamp in both formats using the UTC_TIMESTAMP() function and retrieves the row to demonstrate that the timestamps were correctly stored.

Related Functions

The following are some functions related to the MariaDB UTC_TIMESTAMP() function:

• MariaDB UNIX_TIMESTAMP() function is used to get the Unix timestamp for a given date and time.
• MariaDB FROM_UNIXTIME() function is used to convert a Unix timestamp to a date and time value.
• MariaDB NOW() function is used to get the current date and time in the server’s time zone.
• MariaDB CURRENT_TIMESTAMP() function is used to get the current date and time in the server’s time zone, with the option to specify a precision.
• MariaDB SYSDATE() function is an alias for the NOW() function and returns the current date and time in the server’s time zone.

Conclusion

The UTC_TIMESTAMP() function in MariaDB is a versatile tool for working with timestamps in the UTC time zone, which is essential for applications that require consistent and standardized time representations across different systems or locations. By understanding its syntax, usage, and the examples provided, you can effectively incorporate this function into your SQL queries and database operations, ensuring accurate and reliable time-related data management.