Suggestions for dealing with time offsets in mysql

Question

Distilling this project down to the simplest of terms;

1. Users click a button, a record is made with a timestamp of NOW().
2. NOW() of course equals the time on the server of record creation.
3. I need to show them stats based on their timezone, not mine.

What is the best method for dealign with time zone offsets in MySql? Is there a specific field format that is designed to deal with an offset?

I will need to run things along the lines of:

   SELECT DATE_FORMAT(b_stamp, '%W') AS week_day, count(*) AS b_total, b_stamp
      FROM table
      WHERE
   (b_stamp >= DATE_SUB(NOW(), INTERVAL 7 DAY))
      AND
   (user_id = '$user_id') GROUP BY week_day ORDER BY b_stamp DESC

I would rather not ask the user what time zone they are in, I assume JS is the only way to pull this data out of the browser. Maybe if they are on a mobile device, and this is not a web based app, I could get it there, but that may not be the direction this goes in.

I am considering the best way may be to determine their offset, and set a variable to "server_time" +/- their_offset. This makes it appear as if the server is in a different location. I believe this would be best, as there would be no additional +/- logic I need to add to the code, muddying it and making it ugly.

On the other hand, that puts the data in the database with time stamps that are all over the board.

Suggestions?

Answer 1

You can use javascript to get timezone from client as follows:

var timeZone=(new Date().gettimezoneOffset()/60)*(-1);

print the variable out and test before using it. I think this will be your simplest bet.

Answer 2

Other than using JS, you could get the time zone of their IP address (using something like ip2location) then use MySQL's CONVERT_TZ() function.

Answer 3

You will need an IP to timezone data similar to what is offered by IP2Location