How to Convert datetime to UTC in MySQL

Last updated on July 3rd, 2024 at 09:00 am

Often websites and apps need to work in different time zones. In this case, you will need to maintain date/time information using a single timezone to avoid confusion. Universal Coordinated Time (UTC) is the global standard time used and understood by a large number of people. So you may need to change timezone to UTC or set timezone to UTC. In this article, we will look at how to convert datetime to UTC in MySQL.

Table of Contents

How to Convert datetime to UTC in MySQL

Here are the steps to convert datetime to UTC in MySQL. You can easily change datetime to UTC using convert_tz function.

Here is the syntax for convert_tz

convert_tz(date_value, original_timezone, new_timezone)

In the above function, you can specify date value as a literal string, system function or column name, its original time zone, as well as its new time zone. The original and new time zones can be specified using offsets or time zone names.

Using Literal Strings

Here is an example to convert date time value from local time zone (GMT+10:00) to UTC(+00:00). By default, you need to specify original (+10:00) and new time zones (+00:00) as offsets from UTC. In the following example, we have input datetime value as a literal string.

mysql> SELECT CONVERT_TZ('2018-06-15 12:00:00','+10:00','+00:00');
 +-----------------------------------------------------+
 | CONVERT_TZ('2018-06-15 12:00:00','+00:00','+10:00') |
 +-----------------------------------------------------+
 | 2018-06-15 02:00:00                                 |
 +-----------------------------------------------------+

Similarly, you can also convert date, time, date time columns using convert_tz. Here’s an example to change time zone of order_date column in sales table, from UTC to EST

mysql> select convert_tz(order_date,'+10:00','+00:00') from sales;
+------------------------------------------+
| convert_tz(order_date,'+10:00','+00:00') |
+------------------------------------------+
| 2020-05-03 19:00:00                      |
| 2020-05-04 19:00:00                      |
| 2020-05-05 19:00:00                      |
| ...                                      |
| 2020-05-13 19:00:00                      |
+------------------------------------------+

You can also specify time zones instead of offsets. However, in this case, you will need to download and install MySQL time zones on your server. Here’s an example to convert EST to UTC timezone by specifying time zone names instead of offset values.

mysql> select convert_tz('2020-09-17 03:00:00','US/Eastern','UTC');

Using Column Names

Let us say you have the following table with order_date as datetime column.

mysql> create table sales(id int, amount int, order_date datetime);
mysql> insert into sales(id, amount, order_date) values(1, 150, '2024-07-03 14:20');
mysql> insert into sales(id, amount, order_date) values(2, 250, '2024-07-03 16:40');
mysql> insert into sales(id, amount, order_date) values(3, 200, '2024-07-03 20:25');

mysql> select * from sales;
+------+--------+---------------------+
| id   | amount | order_date          |
+------+--------+---------------------+
|    1 |    150 | 2024-07-03 14:20:00 |
|    2 |    250 | 2024-07-03 16:40:00 |
|    3 |    200 | 2024-07-03 20:25:00 |
+------+--------+---------------------+

Here is an SQL query where we have converted all values of order_date column to UTC time zone.

mysql> select id, amount, convert_tz(order_date,@@session.time_zone,'+00:00') as order_date from sales;
+------+--------+---------------------+
| id   | amount | order_date          |
+------+--------+---------------------+
|    1 |    150 | 2024-07-03 08:50:00 |
|    2 |    250 | 2024-07-03 11:10:00 |
|    3 |    200 | 2024-07-03 14:55:00 |
+------+--------+---------------------+

In this case, when MySQL reads and displays each row, convert_tz() will read and convert the value of order_date column and display the after-conversion value in query result.

Using System Functions & Variables

You can also use convert_tz with system functions such as now() or system variables. The following query will convert local time to UTC. The now() function will return current date and time, and local time zone is stored in session.time_zone variable.

mysql> SELECT CONVERT_TZ( NOW(), @@session.time_zone, '+00:00' );
+----------------------------------------------------+
| CONVERT_TZ( NOW(), @@session.time_zone, '+00:00' ) |
+----------------------------------------------------+
| 2024-07-03 08:48:35                                |
+----------------------------------------------------+

Hopefully, now you can convert datetime to UTC in MySQL. Similarly, you can also convert UTC to local time in MySQL.

Conclusion

In this article, we have learnt how to convert datetime to UTC using convert_tz() function. You can also use it to convert datetime values to any other time zones. Typically, database programmers and web developers store all information using a single time zone, in their databases. When a web server sends it to the client browser, it is automatically converted and displayed as local time, by the web browser itself. It is important if your website or app is used by people in multiple time zones. It is also required to factor things like daylight savings.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Convert UTC to Local time in MySQL
How to Get Current Date and Time in MySQL
How to Group By Month in MySQL
How to Remove NOT NULL Constraint in MySQL