How to use the MySQL UTC_TIMESTAMP() function

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

Posted on

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

Syntax

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

UTC_TIMESTAMP()

The UTC_TIMESTAMP() function does not take any parameters. The UTC_TIMESTAMP() function returns a datetime value that represents the current date and time in UTC. The format of the datetime value is ‘YYYY-MM-DD HH:MM:SS’. 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.

Examples

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

Example 1: Get the current date and time in UTC

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

SELECT UTC_TIMESTAMP() AS result;

This query will return the current date and time in UTC. The query will return ‘2023-01-15 10:02:34’ if the current date and time in UTC is January 15, 2023 10:02:34.

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

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

SELECT UTC_TIMESTAMP() = NOW() AS result;

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

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

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

SELECT CONVERT_TZ(UTC_TIMESTAMP(), '+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 and time in UTC to the date and 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 ‘2023-01-15 05:02:34’ if the current date and time in UTC is 10:02:34, and the current date and time in New York, USA is 05:02:34.

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

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

SELECT DATE_FORMAT(UTC_TIMESTAMP(), '%d/%m/%Y %h:%i:%s %p') 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 and time in UTC as ‘DD/MM/YYYY HH:MM:SS AM/PM’. The query will return ‘15/01/2023 10:02:34 AM’ if the current date and time in UTC is 10:02:34.

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

We can use the UTC_TIMESTAMP() function to get the current date and 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_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 HOUR)) AS result;

This query will use the TIMESTAMPDIFF() function, which returns the difference between two date 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_TIMESTAMP() function to indicate the current date and time in UTC, and the DATE_ADD() function, which adds a specified interval to a date or datetime value, to indicate the next hour in UTC. The query will return 3546 if the current date and 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_TIMESTAMP() function, and can be useful for working with dates, times, and time zones. Here are some of them:

  • NOW(): This function returns the current date and time in the local time zone. For example, NOW() returns ‘2023-01-15 10:02:34’ if the current date and 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_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.
  • 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-15 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 %p') returns ‘15/01/2023 10:02:34 AM’, which is the date and time formatted as ‘DD/MM/YYYY HH:MM:SS AM/PM’.
  • TIMESTAMPDIFF(): This function returns the difference between two date 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 date and time values.

Conclusion

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