Sometimes you may need to change UTC to local time in MySQL. In this article, we will look at how to convert UTC to local time in MySQL using convert_tz function.
How to Convert UTC to Local Time in MySQL
You can easily convert UTC to local time in MySQL using convert_tz function. Here is the syntax for convert_tz function.
convert_tz(date_value, original_timezone, new_timezone)
In the above function you need to specify date value as a literal string, system function or column name, its original time zone, as well as its new time zone.
Here is an example to convert date time value from UTC to local time zone (GMT+10:00). By default, you need to specify original (+00:00) and new time zones (+10:00) as offsets from UTC.
mysql> SELECT CONVERT_TZ('2018-06-15 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2018-06-15 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2018-06-15 22: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 Paris timezone by specifying time zone names instead of offset values.
mysql> select convert_tz('2020-09-17 03:00:00','US/Eastern','Europe/Paris');
You can also use system functions like now() in convert_tz function to convert current date time to other time zones, as shown below.
mysql> select convert_tz(now(),'+00:00','-05:00'); +-------------------------------------+ | convert_tz(now(),'+00:00','-05:00') | +-------------------------------------+ | 2020-09-17 04:45:07 | +-------------------------------------+
Also Read : How to Group By Month in MySQL
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,'+00:00','-05:00') from sales; +------------------------------------------+ | convert_tz(order_date,'+00:00','-05: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 | +------------------------------------------+
Hopefully, now you can change MySQL time zone in query and convert date, time, datetime values from one timezone to another.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
