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