How to Convert datetime to UTC in MySQL

Sometimes 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.


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.

Also read : How to Convert UTC to Local time in MySQL

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.

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                      |
+------------------------------------------+

Also read : How to Get Current Date and Time in MySQL

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');

Hopefully, now you can convert datetime to UTC in MySQL.

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

mm

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!