:::: MENU ::::

How to Convert UTC Dates to Local Time in MySQL, Accounting for Daylight Saving

0.00 avg. rating (0% score) - 0 votes


When working with a MySQL database, you may often store dates in UTC format to ensure consistency and avoid timezone-related issues. However, there may be cases where you need to display these dates in a specific local time, like Spanish local time, taking daylight saving time into account. In this blog post, we’ll show you how to achieve this using the CONVERT_TZ() function in MySQL.

Step 1: Load timezone data into your MySQL database

Before you can use the CONVERT_TZ() function to convert dates to a specific timezone, you need to ensure that the timezone information is loaded into your MySQL database. If it’s not already loaded, follow these steps:

  1. Open your terminal or command prompt.
  2. Execute the following command:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
  1. You’ll be prompted for your MySQL root password. Enter your password, and the timezone data will be loaded into your MySQL database.

Step 2: Use the CONVERT_TZ() function

To convert UTC dates to Spanish local time Now that the timezone data is loaded, you can use the CONVERT_TZ() function to convert the UTC dates to Spanish local time. Spain has two timezones: Central European Time (CET) and Central European Summer Time (CEST). CET is UTC+1, while CEST is UTC+2.

Here’s an example SQL query that retrieves all columns from the sales_order table and converts the created_at column to Spanish local time:

select HOUR(CONVERT_TZ(created_at, 'UTC', 'Europe/Madrid')) as day_hour, COUNT(entity_id) as order_number from sales_order
where created_at > DATE_SUB(NOW(),INTERVAL 1 DAY)
group by day_hour
order by day_hour

This will give you the distribution of the orders in your database during the day, so you can figure it out your sales peak hours taking into account proper time offsets depending on the date the order was made.

Now you can put the result of the query in Excel, and create a chart like this:

Hey! Qué opinas sobre el artículo?