SQL Tip – CONVERT_TZ in MySQL

Working with time zones has generic and platform-specific challenges. This SQL tip involves some design aspect not only the specific SQL code. Namely taking care to have a method to determine the original local used to create the date time entry.

The Generic Part

All DB platforms presently support using time zones. The DB client that connects to the DB has a local, for example America/Montreal, and the date and time retrieved from the database has an offset automatically applied. Regardless of the location the DB client the times appear correctly to match a date time between two time zones. So, appointments work correctly.

The Difficult Part

Two clients can record a date and time, but you might not want to automatically apply an offset to the data. You may want to apply the original local time. Think of the example of a package company in Canada wanting to know which deliveries were made after 10 AM. This would mean you would need to know what the local time for each record was.

A Good Design Practice Is Saving the Local Used

Adding the local used to record a date/time is helpful. It will make the querying easier. MySQL 8 has a function to convert a time zone dynamically. You will probably avoid problems if you require the time zone to be populated in the record. A future developer will notice and need to specify a local. Also it is much more difficult try to determine what the local time zone was used after the fact. Imaging trying to use a province or a state to try to convert to another time zone.

A MySQL Example

This example is a table with a delivery time and a delivery local. Two were delivered in Montreal one in Vancouver. There are three deliveries, and we would like to know which ones were delivered after 10 AM local time. A simple select with a formatted date would give us this result.

So the intuitive query is….

SELECT Client, DeliveryTime, DeliveryLocal
  FROM sqlclinic_examples.Delivery
 where HOUR(DeliveryTime) >= 10;

And the result is…

Now you can see that there are 2 records. What is not immediately clear is that the first record appears incorrectly. The DB Client applied my local “America/Montreal” and included the record from Vancouver. The time of 11:45 AM in Montreal is actually 8:45 AM in Vancouver. So the first record was not a delivery that was late.

We need to apply the local in the DeliveryLocal to the DeliveryTime field to correct this problem.

CONVERT_TZ()

There is a CONVERT_TZ function. You can use it to change the date time from your current default local to another local. In our case we would like to convert the Local from America/Montreal to the value in the DeliveryLocal field.

So this is what the query looks like:

SELECT Client,
       CONVERT_TZ (DeliveryTime,
                   'America/Montreal',
                   DeliveryLocal) LocalDeliveryTime,
       DeliveryLocal
  FROM sqlclinic_examples.Delivery
where HOUR(CONVERT_TZ(DeliveryTime,
                      'America/Montreal',
                      DeliveryLocal)) >= 10;

Notice how the function is used in both the fields selected and the where clause of the select statement.

And the result is…

So that is how you get the answer.

Have a nice day.

Published by Marek

Just a Database Administrator from Montreal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: