How the UTC_DATE() function works in Mariadb?

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

Syntax

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

UTC_DATE()

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

Examples

Example 1: Retrieving the Current UTC Date

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

SELECT UTC_DATE() AS current_utc_date;

Output:

+------------------+
| current_utc_date |
+------------------+
| 2024-03-10       |
+------------------+

The UTC_DATE() function returns the current date in the UTC time zone as a DATE value.

Example 2: Comparing UTC_DATE() with CURRENT_DATE()

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

SELECT UTC_DATE(), CURRENT_DATE() AS local_date;

Output:

+------------+------------+
| UTC_DATE() | local_date |
+------------+------------+
| 2024-03-10 | 2024-03-10 |
+------------+------------+

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

Example 3: Using UTC_DATE() in a Query

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

DROP TABLE IF EXISTS example;
CREATE TABLE example (id INT, data VARCHAR(100), created_at DATE);
INSERT INTO example VALUES (1, 'Record 1', '2024-03-10'), (2, 'Record 2', '2024-03-11'), (3, 'Record 3', '2024-03-12');

SELECT * FROM example WHERE created_at = UTC_DATE();

Output:

+------+----------+------------+
| id   | data     | created_at |
+------+----------+------------+
|    1 | Record 1 | 2024-03-10 |
+------+----------+------------+

In this example, the query selects records from the example table where the created_at date matches the current UTC date returned by the UTC_DATE() function.

Example 4: Using UTC_DATE() in a Trigger

This example demonstrates how to use the UTC_DATE() function within a trigger to automatically record the UTC date 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 DATE DEFAULT UTC_DATE());

DELIMITER $$
CREATE TRIGGER example_insert_trigger
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.updated_at = UTC_DATE();
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 | 2024-03-10 |
+----+------------------+------------+

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 date using the UTC_DATE() function. When a record is inserted or updated, the trigger automatically records the UTC date when the operation occurred.

Related Functions

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

• MariaDB UTC_TIME() function returns the current time in the UTC time zone.
• MariaDB UTC_TIMESTAMP() function returns the current date and time in the UTC time zone.
• MariaDB CURRENT_DATE() function returns the current date in the server’s local time zone.
• MariaDB CURRENT_TIME() function returns the current time 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_DATE() function in MariaDB is a useful tool for retrieving the current date 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 date information, filter data based on UTC dates, or synchronize date values across different time zones, the UTC_DATE() function provides a convenient way to work with dates in the universal time zone standard.