How to use the MySQL UTC_TIME() function

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

Posted on

In this article, we will learn how to use the MySQL UTC_TIME() function, which returns the current time 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 time and time zones.

Syntax

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

UTC_TIME()

The UTC_TIME() function does not take any parameters. The UTC_TIME() function returns a time value that represents the current time in UTC. The format of the time value is ‘HH:MM:SS’. For example, UTC_TIME() returns ‘10:02:34’ if the current time in UTC is 10:02:34.

Examples

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

Example 1: Get the current time in UTC

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

SELECT UTC_TIME() AS result;

This query will return the current time in UTC. The query will return ‘10:02:34’ if the current time in UTC is 10:02:34.

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

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

SELECT UTC_TIME() = CURTIME() AS result;

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

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

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

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

This query will use the CONVERT_TZ() function, which converts a time or datetime value from one time zone to another, to convert the current time in UTC to the time 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 ‘05:02:34’ if the current time in UTC is 10:02:34, and the current time in New York, USA is 05:02:34.

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

We can use the UTC_TIME() function to get the current time in UTC and format it in a different way. For example, if we want to get the current time in UTC and format it as ‘HH:MM:SS AM/PM’, we can use the following query:

SELECT DATE_FORMAT(UTC_TIME(), '%h:%i:%s %p') AS result;

This query will use the DATE_FORMAT() function, which returns the time or datetime value formatted according to a specified format, to format the current time in UTC as ‘HH:MM:SS AM/PM’. The query will return ‘10:02:34 AM’ if the current time in UTC is 10:02:34.

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

We can use the UTC_TIME() function to get the current time in UTC and use it in a calculation. For example, if we want to get the number of seconds until the next hour in UTC, we can use the following query:

SELECT TIMESTAMPDIFF(SECOND, UTC_TIME(), DATE_ADD(UTC_TIME(), INTERVAL 1 HOUR)) AS result;

This query will use the TIMESTAMPDIFF() function, which returns the difference between two time or datetime values, in a specified unit, to calculate the number of seconds until the next hour in UTC. The query will use the UTC_TIME() function to indicate the current time in UTC, and the DATE_ADD() function, which adds a specified interval to a time or datetime value, to indicate the next hour in UTC. The query will return 3546 if the current time in UTC is 10:02:34, and the next hour in UTC is 11:00:00.

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

  • CURTIME(): This function returns the current time in the local time zone. For example, CURTIME() returns ‘10:02:34’ if the current time in the local time zone is 10:02:34.
  • UTC_DATE(): This function returns the current date in UTC. For example, UTC_DATE() returns ‘2023-01-15’ if the current date in UTC is January 15, 2023.
  • 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 time 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-15 05:02:34’, which is the time in New York, USA, corresponding to the time in UTC.
  • DATE_FORMAT(): This function returns the time or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15 10:02:34', '%h:%i:%s %p') returns ‘10:02:34 AM’, which is the time formatted as ‘HH:MM:SS AM/PM’.
  • TIMESTAMPDIFF(): This function returns the difference between two time or datetime values, in a specified unit. For example, TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') returns 60, which is the number of seconds between the two time values.

Conclusion

In this article, we learned how to use the MySQL UTC_TIME() function, which returns the current time 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 time and time zones.