How the UTC_TIME() function works in Mariadb?

The UTC_TIME() function in MariaDB is used to retrieve the current time in the UTC (Coordinated Universal Time) time zone. It returns the time portion of the current date and time value in the UTC time zone, without the date component.

Syntax

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

UTC_TIME()

The UTC_TIME() function does not take any arguments. It returns a TIME value representing the current time in the UTC time zone.

Examples

Example 1: Retrieving the Current UTC Time

This example demonstrates how to use the UTC_TIME() function to get the current time in the UTC time zone.

SELECT UTC_TIME() AS current_utc_time;

Output:

+------------------+
| current_utc_time |
+------------------+
| 07:39:52         |
+------------------+

The UTC_TIME() function returns the current time in the UTC time zone as a TIME value.

Example 2: Comparing UTC_TIME() with CURRENT_TIME()

This example shows the difference between the UTC_TIME() and CURRENT_TIME() functions, which both return the current time but in different time zones.

SELECT UTC_TIME() AS utc_time, CURRENT_TIME() AS local_time;

Output:

+------------------+
| current_utc_time |
+------------------+
| 07:39:52         |
+------------------+

In this example, the UTC_TIME() function returns the current time in the UTC time zone, while the CURRENT_TIME() function returns the current time in the server’s local time zone. The output shows the difference (if any) between the UTC time and the local time.

Example 3: Using UTC_TIME() in a Query

This example demonstrates how to use the UTC_TIME() function within a query to filter or manipulate data based on the current UTC time.

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT, data VARCHAR(100), created_at TIME);
INSERT INTO example VALUES (1, 'Record 1', '15:00:00'), (2, 'Record 2', '16:30:00'), (3, 'Record 3', '18:45:00');

SELECT * FROM example WHERE created_at >= UTC_TIME();

Output:

+------+----------+------------+
| id   | data     | created_at |
+------+----------+------------+
|    1 | Record 1 | 15:00:00   |
|    2 | Record 2 | 16:30:00   |
|    3 | Record 3 | 18:45:00   |
+------+----------+------------+

In this example, the query selects records from the example table where the created_at time is greater than or equal to the current UTC time returned by the UTC_TIME() function.

Example 4: Using UTC_TIME() in a Trigger

This example demonstrates how to use the UTC_TIME() function within a trigger to automatically record the UTC time when a record is inserted or updated.

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100), updated_at TIME DEFAULT UTC_TIME());

DELIMITER $$
CREATE TRIGGER example_insert_trigger
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.updated_at = UTC_TIME();
END$$
DELIMITER ;

INSERT INTO example (data) VALUES ('Record 1');
UPDATE example SET data = 'Updated Record 1';

SELECT * FROM example;

Output:

+----+------------------+------------+
| id | data             | updated_at |
+----+------------------+------------+
|  1 | Updated Record 1 | 07:41:00   |
+----+------------------+------------+

In this example, a trigger example_insert_trigger is created on the example table. Before any insert operation, the trigger sets the updated_at column to the current UTC time using the UTC_TIME() function. When a record is inserted or updated, the trigger automatically records the UTC time when the operation occurred.

Related Functions

The following are a few functions related to the MariaDB UTC_TIME() function:

• MariaDB UTC_DATE() function returns the current date in the UTC time zone.
• MariaDB UTC_TIMESTAMP() function returns the current date and time in the UTC time zone.
• MariaDB CURRENT_TIME() function returns the current time in the server’s local time zone.
• MariaDB CURRENT_DATE() function returns the current date in the server’s local time zone.
• MariaDB CURRENT_TIMESTAMP() function returns the current date and time in the server’s local time zone.

Conclusion

The UTC_TIME() function in MariaDB is a useful tool for retrieving the current time in the UTC time zone. By understanding the syntax and usage examples, you can easily incorporate this function into your SQL queries, stored procedures, triggers, and other database operations. Whether you need to log time information, filter data based on UTC times, or synchronize time values across different time zones, the UTC_TIME() function provides a convenient way to work with times in the universal time zone standard.