Intro
When we work with elements such us “date” or “date-time”, developers make a lot of dangerous assumptions.
Some of them assume the time zone within the data. Consider that, if you have two customers, one in Spain and another in Colombia, and the developer is in Argentina, what time do you use to log requests from clients?
The Naive Case
Take the next example. Your boss gives you a task: create a system for taking measures (download speed, for example) in a database, for a future report. He says that the report must be segmented every hour.
So you create a simple TABLE with 2 fields:
- The value of the measurement
- And the date of that measurement in this format (2015-11-22 23:22:01)
CREATE TABLE `report` (
`date_measurement` datetime DEFAULT NULL,
`value_measurement` int(11) DEFAULT NULL
)
Next, you begin to collect data, for that report.
| date_measurement | value_measurement |
|---|---|
| 22/11/2015 23:00 | 30 |
| 22/11/2015 23:50 | 55 |
| 22/11/2015 23:40 | 56 |
| 22/11/2015 23:40 | 40 |
| 22/11/2015 23:40 | 49 |
| 23/11/2015 00:10 | 47 |
| 23/11/2015 00:15 | 52 |
| 23/11/2015 00:40 | 51 |
| 23/11/2015 01:10 | 35 |
After a month, you generate a report such as this (in Argentina)
SELECT COUNT(*), HOUR(TIMEDIFF(NOW(), `date_measurement` )) AS DIFF_HOUR
FROM report
GROUP BY HOUR(TIMEDIFF(NOW(), `date_measurement` ) )
| DIFF_HOUR | COUNT(*) |
|---|---|
| 299 | 5 |
| 300 | 3 |
| 301 | 1 |
But when you install the system to generate the report into the client (in Colombia), a different report is generated.
| DIFF_HOUR | COUNT(*) |
|---|---|
| 301 | 5 |
| 302 | 3 |
| 303 | 1 |
The query is the same but results are different. What’s wrong?
You are using a simple and innocent SQL sequence and that generates different data.
Have you seen the problem, yet?
After checking too many configurations, and some possible mistakes, you are sure that there is no difference between the customer and the development environment.
And some partner makes a little question. What is the Time Zone (TZ) in the environment?
When you check, you realize in your computer, that TZ is America/buenos aires (UTC-3) but the customer’s TZ is America/colombia (UTC-5).
So when you generate the report, (with a simple naive sequence) that generates a different report because the environment is in a different time.
What can we do?
Here’s some advice.
Speak and consult with your partner (two heads think better that one). Probably, your partner has a similar problem. And when you speak with another person, you are forced to describe the problem and this exercise sorts your ideas.
Check what TZ your client works with and configure your environment equally.
Databases are not the only ones possibly wrong. Some programming languages configure your personal TZ ( PHP, python, etc..)
If the Application needs to work with many TZ ( Colombia, Chile, Spain, etc..) you have to take a way.
- ADD COLUMN TO INDICATE THE TZ
- Add a field to indicate the UTC (UTC-3, UTC+5) that is used, In this case, you must convert every record to some TZ before comparing.
- ADD COLUMN TO INDICATE THE TIME
- The date you want to store, save it to unix timestamp. For example save the data “1979-04-22T13:40:12” as number 293636412
- WORK EVERYTHING IN UTC-0.
- In this case, you can use the dates in human format. But they must be saved in UTC-0 time. For example, if in Argentina (UTC-3) the current time is “2016-11-08 12:00:29”, you must save that “2016-11-08 15:00:29”.
Conclusion
In this document, we introduce the dangers of working with different Time Zone dates.
You should never take them naively. It is very easy to confuse the TZ with the data.
The motivation for writing about the TZ was a problem that we had at Intraway with a product in bMonitor, installed in Chile.
For more information, you can check these links:
- http://php.net/manual/en/refs.calendar.php
- https://docs.python.org/3/library/datetime.html
- http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
- https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
We hope this information has been useful for you.
