query database for results within a certain time using the user's timezone

How can I query a set of results that belong to a specific time period according to the user's time.

For example: "select * from table where datestamp l开发者_StackOverflowike ".date("Y-m-d",strtotime("-1 day"))."%"

would give me results within the past 24 hours based on the server time. How can I do this query to be based on the user's time rather than the server's time?

You can do this in 3 steps:

1. Detect the client timezone

This is difficult: Different browsers use different acronyms and conventions for representing names of timezones. You should use an existing implementation, like jsTimezoneDetect.

2. Pass the timezone information to PHP

If you need to use timezone dependent PHP functions (like date() in your exemple), you can set the timezone with:

date_default_timezone_set($TZ);

Where $TZ is the variable where you stored the timezone from the client request.

3. Pass the timezone information to MySQL

If you need to use timezone dependent MySQL functions, you can set the timezone for the current MySQL session with:

mysql_query("SET time_zone = $TZ");

In order to find out how to get your web client timezone, please read this question

Ask your user which timezone he's in, then subtract/add the offset to your server's time.

"The last 24 hours" are the same all over the world though, so I'd rather change the query to encompass the last 24 hours, not the same day:

SELECT * FROM `table`
WHERE `datestamp` BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()