0

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 like ".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?

John
  • 3
  • 1

3 Answers3

0

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

Community
  • 1
  • 1
Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
0

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()
deceze
  • 510,633
  • 85
  • 743
  • 889
0

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");
Jerome
  • 2,350
  • 14
  • 25
  • This sets the timezone within PHP or MySQL? How can I set it for both? And how would I get the user's timezone – John Jun 02 '11 at 07:19
  • The DATE() function I am using is a PHP function not a mysql function – John Jun 02 '11 at 17:43