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!
