2

I have a script which has to do a "Google Analytics" like task. Basically display visitor statistics for a user.

I want to generate a report in the time zone of the user who is requesting it.

So far I have written a code with this:

SET time_zone = timezone;

What it does it sets the time zone per each MySQL connection. If a user retrieves data with timestamp the timestamp is converted to the timezone of the connection. I am storing the UTC in the timestamp.

So everything seems to work. But some people are saying that this is a wrong approach. Because multiple user can't connect to the database with different time_zone setting.

But the MySQL doc says:

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement

However they keep insisting that you should not do anything with time zone in MySQL at all. You should do it all in your (for example) PHP code. Here a similar question with this answer.

But how can I do it in the PHP code? I mean I know how to convert a time with a time zone in PHP but it's not like I am retrieving a single row.

I am retrieving thousands of rows and GROUP them by the date in the timestamp field:

SELECT ... 
FROM logs 
WHERE 
user_id = :user_id
AND timestamp >= CURDATE() 
GROUP BY DATE(timestamp)

It is very important that MySQL is using the index of timestamp because I have millions of. Does the index work even though I am using a function on the timestamp GROUP BY DATE(timestamp)? If not how else could I accomplish this?

So how should I do this all? I would be really thankful for some advice.

Community
  • 1
  • 1
yoshi
  • 1,287
  • 3
  • 15
  • 28
  • 1
    The timestamps from the clients should be already converted to gmt. A time stamp from a computer in London and a computer in Hong Kong should be the same even though their local times are not. Does that help to clarify what to do? Basically, nothing. The client should be doing the sloppy work. – Octopus Jul 25 '14 at 07:49
  • The only thing to do on the DB is, define a Timezone (maybe UTC) that all data is stored in and that's it. Do the conversion in PHP. – ToBe Jul 25 '14 at 07:58

1 Answers1

0

Now User converts all timestamps to his tz and uses timestamp(*user) >= CURDATE(*user). I think the other way is to 

convert timestamp(user) to timestamp(server)

and use 

timestamp(*server) >= CURDATE(*server)

example 

dates =(5,6,7)

(convert to my tz) dates-2 = (3,4,5) (check constrain directly with this array )biggerThan3 result=(4,5) 2nd way (convert constraint to ts server) biggerThan3 -> biggerThan(3+2)5 (check constraint with server array) dates =(5,6,7) result=(6,7) result can be converted to (4,5)

mike78
  • 1