How to Convert UTC to Local Time in MySQL

Last updated on July 5th, 2024 at 08:57 am

MySQL allows you to store date & time values pertaining to different time zones. Sometimes you may need to change UTC to local time in MySQL. This is commonly required if your users are located in different time zones. In such cases, web developers convert all date & time information to UTC and then store it in database. While retrieving this information, they convert UTC to local time before sending it to the client web browser. 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

Actually, if you are displaying datetime values on your web pages, then the web browser automatically converts datetime values to local time zone. So all you need to do is retrieve datetime values in UTC and send it to the client. However, if you need to convert datetime values for other purposes, then you will need to convert UTC to local time.

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.

1. Using String Literals

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

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

2. Using MySQL Functions

You can also use system functions like now() in convert_tz function to convert current date time to other time zones, as shown below. In this case, convert_tz will work on the result of MySQL functions like now(), curdate(), etc.

mysql> select convert_tz(now(),'+00:00','-05:00');
+-------------------------------------+
| convert_tz(now(),'+00:00','-05:00') |
+-------------------------------------+
| 2020-09-17 04:45:07                 |
+-------------------------------------+

3. Using Columns

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. Let us say you have the following MySQL table.

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'),
       (2, 250, '2024-07-03 16:40'),
       (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 |
+------+--------+---------------------+

mysql> select convert_tz(order_date,'+00:00','-05:00') from sales;
+------------------------------------------+
| convert_tz(order_date,'+00:00','-05:00') |
+------------------------------------------+
| 2024-07-03 09:20:00                      |
| 2024-07-03 11:40:00                      |
| 2024-07-03 15:25:00                      |
+------------------------------------------+

Hopefully, now you can change MySQL time zone in query and convert date, time, datetime values from one timezone to another.

Conclusion

In this article, we have learnt how to convert datetime value in UTC to local time zone. Generally, websites store all datetime values in UTC time zone and convert it to different time zones, in case their users are located in different time zones. You can use convert_tz() function for converting datetime values from one time zone to another. It works well with string literals, function results as well as columns.

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

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