Sometimes I need to design application so that people from different timezones will be able to work with same shared data. This data records will have DATETIME columns. Application will need to display same date to different people with different timezones. Ability to filter data records by date ranges should be also implemented.
In such cases it is very important to design application correctly from the begining, because it will be hard to make such important changes later, when some functionality will be ready.
Here is the way I usually do it.
First we need to decide in which timezone will we store DATETIME values in the DB. I usually store all date values in the database in UTC timezone. When selection is done, it is important to care about the way how your application connects to database. For this needs, after connecting to DB my application runs the following init statement:
SET time_zone = UTC
After running this statement connection will work in UTC timezone. For example, running MySQL’s NOW() will return value in UTC timezone.
Most common operations our application will need to do will be:
– searching some records by user’s date range
– displaying dates in user’s time zone
Let’s imagine that our user’s timezone is ‘Europe/Kiev’. Also, let’s imagine that we have a simple DB table `news` which has 3 columns: INT `id`, DATETIME `created_ts`, VARCHAR `title`.
Task 1 – find ID’s of the records which were created today (it’s ‘2015-03-15 08:00:00’ in user’s timezone).
SELECT `id` FROM `news`
WHERE DATE(`created_ts`) = DATE(CONVERT_TZ('2015-03-15 08:00:00', 'Europe/Kiev', 'UTC'));
Task 2 – select last 5 records in the following format: id, title, local_date.
SELECT `id`, `title`, CONVERT_TZ(`created_ts`, 'UTC', 'Europe/Kiev') AS local_date FROM `news` ORDER BY `created_ts` DESC LIMIT 5;
It is extremely important to design multi-timezones applications correctly from the begining. However, the solution is very easy, you simply need to follow two important rules all the time:
1) Store all DATE and DATETIME values in DB in single timezone.
2) Do not forget to set correct timezones for each DB connection.
2) Convert DATE and DATETIME values from one timezone to oher when doing requests to DB.
Combining all the mentioned recommendations you can design a multi-timezone MySQL-based web application so that it will be easy to scale it and re-use it’s data for multiple purposes in future.