How to use the MySQL UTC_DATE() function

In this article, we will learn how to use the MySQL UTC_DATE() function, which returns the current date in UTC (Coordinated Universal Time).

Posted on

In this article, we will learn how to use the MySQL UTC_DATE() function, which returns the current date in UTC (Coordinated Universal Time). We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates and time zones.

Syntax

The syntax of the UTC_DATE() function is as follows:

UTC_DATE()

The UTC_DATE() function does not take any parameters. The UTC_DATE() function returns a date value that represents the current date in UTC. The format of the date value is ‘YYYY-MM-DD’. For example, UTC_DATE() returns ‘2023-01-15’ if the current date in UTC is January 15, 2023.

Examples

Let’s see some examples of how to use the UTC_DATE() function in MySQL.

Example 1: Get the current date in UTC

We can use the UTC_DATE() function to get the current date in UTC. For example:

SELECT UTC_DATE() AS result;

This query will return the current date in UTC. The query will return ‘2023-01-15’ if the current date in UTC is January 15, 2023.

Example 2: Get the current date in UTC and compare it with a local date

We can use the UTC_DATE() function to get the current date in UTC and compare it with a local date. For example, if we want to check if the current date in UTC is the same as the current date in Beijing, China, we can use the following query:

SELECT UTC_DATE() = CURDATE() AS result;

This query will compare the current date in UTC with the current date in the local time zone, which is Beijing, China. The query will return 0 (false) if the current date in UTC is different from the current date in Beijing, China, or 1 (true) if they are the same.

Example 3: Get the current date in UTC and convert it to a local date

We can use the UTC_DATE() function to get the current date in UTC and convert it to a local date. For example, if we want to get the current date in UTC and convert it to the date in New York, USA, we can use the following query:

SELECT CONVERT_TZ(UTC_DATE(), '+00:00', '-05:00') AS result;

This query will use the CONVERT_TZ() function, which converts a date or datetime value from one time zone to another, to convert the current date in UTC to the date in New York, USA. The query will use the ‘+00:00’ argument to indicate the UTC time zone, and the ‘-05:00’ argument to indicate the New York time zone. The query will return ‘2023-01-14’ if the current date in UTC is January 15, 2023, and the current date in New York, USA is January 14, 2023.

Example 4: Get the current date in UTC and format it in a different way

We can use the UTC_DATE() function to get the current date in UTC and format it in a different way. For example, if we want to get the current date in UTC and format it as ‘DD/MM/YYYY’, we can use the following query:

SELECT DATE_FORMAT(UTC_DATE(), '%d/%m/%Y') AS result;

This query will use the DATE_FORMAT() function, which returns the date or datetime value formatted according to a specified format, to format the current date in UTC as ‘DD/MM/YYYY’. The query will return ‘15/01/2023’ if the current date in UTC is January 15, 2023.

Example 5: Get the current date in UTC and use it in a calculation

We can use the UTC_DATE() function to get the current date in UTC and use it in a calculation. For example, if we want to get the number of days until the next New Year’s Day in UTC, we can use the following query:

SELECT DATEDIFF('2024-01-01', UTC_DATE()) AS result;

This query will use the DATEDIFF() function, which returns the difference between two date values, to calculate the number of days until the next New Year’s Day in UTC. The query will use the ‘2024-01-01’ argument to indicate the next New Year’s Day, and the UTC_DATE() function to indicate the current date in UTC. The query will return 352 if the current date in UTC is January 15, 2023.

There are some other functions that are related to the UTC_DATE() function, and can be useful for working with dates and time zones. Here are some of them:

  • CURDATE(): This function returns the current date in the local time zone. For example, CURDATE() returns ‘2023-01-15’ if the current date in the local time zone is January 15, 2023.
  • UTC_TIME(): This function returns the current time in UTC. For example, UTC_TIME() returns ‘10:02:34’ if the current time in UTC is 10:02:34.
  • UTC_TIMESTAMP(): This function returns the current date and time in UTC. For example, UTC_TIMESTAMP() returns ‘2023-01-15 10:02:34’ if the current date and time in UTC is January 15, 2023 10:02:34.
  • CONVERT_TZ(): This function converts a date or datetime value from one time zone to another. For example, CONVERT_TZ('2023-01-15 10:02:34', '+00:00', '-05:00') returns ‘2023-01-14 05:02:34’, which is the date and time in New York, USA, corresponding to the date and time in UTC.
  • DATE_FORMAT(): This function returns the date or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15 10:02:34', '%d/%m/%Y %H:%i:%s') returns ‘15/01/2023 10:02:34’, which is the date and time formatted as ‘DD/MM/YYYY HH:MM:SS’.

Conclusion

In this article, we learned how to use the MySQL UTC_DATE() function, which returns the current date in UTC. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates and time zones.