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:
- Open your terminal or command prompt.
- Execute the following command:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
- 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: