CODEWITHSUNDEEP
phpmysqldatetimetimezoneunix-timestamp
yoshi
yoshi

Reputation: 1315

PHP, MySQL: SELECT by individual timezone preferences

I have a table which (lets say for example) stores hits:

id |      datetime         |  ...
-----------------------------------
1  |  2014-05-30 15:19:00  |  ...
2  |  2014-06-01 12:14:00  |  ...
3  |  2014-06-04 13:06:00  |  ...
4  |  2014-06-04 17:26:00  |  ...
5  |          ...          |  ...

User can generate reports they can chose a date range. However I want to give the user the ability to use their own timezone preferences.

Currently I use queries like this for example to select today's columns:

... WHERE datetime >= CURDATE()

How can I do this? Is it better to store the date as unixtime? I've read every result on Google but I am confused.

This query will be called a lot so performance is very important

Upvotes: 1

Views: 225

Answers (1)

user1613360
user1613360

Reputation: 1314

You can get the timezone preference from the user as a name of the country and use this to set the timezone accordingly:

SET time_zone = 'Europe/Helsinki';

To create filter for date ranges you can use a normal where condition like this:

select * from table where hit_date>=startdate and hit_date<enddate

Performance:

You can create an index with datetime column which will greatly improve the performance.One thing to remember is it's usually not a good idea to use something which gets updated quite often as an index.

Note:

Let's say you store the page hit date time in your own time zone which is for example Asia/Beijing and the user changes it into 'Europe/Helsinki' in this case you should convert the time zone accordingly before executing the query to retrieve the correct results using 

SELECT CONVERT_TZ('2004-01-01 12:00:00','Asia/Beijing','Europe/Helsinki');

In layman terms you should find what time it was in your timezone before executing the user's query because the database was populated based on your timezone.

Users time zone---------->Your time zone-------------->Query

Upvotes: 1

Related Questions