1

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

yoshi
  • 1,287
  • 3
  • 15
  • 28

1 Answers1

1

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

user1613360
  • 1,280
  • 3
  • 16
  • 42
  • Thank you. But I am trying to avoid something like `CONVERT_TZ(...)` because I think this is not good for performance. Indexes won't work because MySQL has to execute the function before knowing its value... – yoshi Jun 09 '14 at 06:34
  • Data correctness is more important than performance.It's not about the speed in which you fetch the results it's all about how correct the results are.Index can be used for user queries where the user select's the date range. – user1613360 Jun 09 '14 at 06:37
  • Ok, got it. But `SELECT CONVERT_TZ('2004-01-01 12:00:00','Asia/Beijing','Europe/Helsinki');` What if my timezone ist UTC? – yoshi Jun 09 '14 at 09:28
  • You can use SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','GMT') I gave the name of locations as input just to make it clear.You can use GMT,UTC,PST or any other timezone's as inputs. – user1613360 Jun 09 '14 at 09:37
  • I don't know why but its not working: `SELECT CONVERT_TZ(time,'Asia/Beijing','UTC') AS time ... WHERE ...` The query works but for the results in "time" column are always empty – yoshi Jun 09 '14 at 10:37
  • 1
    I think you can use convert_tz(time,'gmt','utc') or convert_tz(time'Asia/Beijing','Europe/London') but you can't mix them.Try it and let me know. – user1613360 Jun 09 '14 at 12:54
  • That was it. I was trying since hours... haha, that simple. Thank you! – yoshi Jun 09 '14 at 18:43