Sometimes you may need to convert MySQL time value from one timezone to another. Here’s how to change MySQL timezone in query using convert_tz() function.
Convert timezone in MySQL query
We will use CONVERT_TZ function to change MySQL timezone in query. Here’s the syntax of CONVERT_TZ function.
convert_tz(value, from_timezone, to_timezone)
In the above function, you need to provide the time value to be converted, the time zone from which you want to convert this value, and the time zone to which you want to convert it.
You can specify time zones as offsets or timezone names.
Bonus Read : How to Change MySQL Server Time Zone
Here’s an example to convert a literal time value from UTC to EST timezone, using timezone offsets of ‘+00:00’ and ‘-05:00’ for UTC and EST respectively.
mysql> select convert_tz('2020-09-17 03:00:00','+00:00','-05:00');
+-----------------------------------------------------+
| convert_tz('2020-09-17 03:00:00','+00:00','-05:00') |
+-----------------------------------------------------+
| 2020-09-16 22: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 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');
Bonus Read : MySQL Show Indexes in Database
You can also convert MySQL date, time and date time functions in CONVERT_TZ function. Here’s an example to change time zone of current time obtained using NOW() function, from UTC to EST
mysql> select convert_tz(now(),'+00:00','-05:00'); +-------------------------------------+ | convert_tz(now(),'+00:00','-05:00') | +-------------------------------------+ | 2020-09-17 04:45:07 | +-------------------------------------+
Bonus Read : How to Compare MySQL databases
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!
