通过访问者的时区获取昨天的mySQL数据

Question

I'm successfully fetching yesterday's mySQL data using

SELECT COUNT(*) as total FROM  track
WHERE  FROM_UNIXTIME(date,'%Y-%m-%d %h:%m:%s') > DATE_ADD(NOW(), INTERVAL -2 DAY) 
AND FROM_UNIXTIME(date,'%Y-%m-%d %h:%m:%s') < DATE_ADD(NOW(), INTERVAL -1 DAY)

However, it uses server's time zone. My server is located in US, If visitor is from a different timezone than US (ex:asia or europe) my yesterday data won't be correct for user. I want to fetch the correct yesterday results based on visitor's time zone. I can get the visitor timezone in php, but I can't figured out how to use it in mySQL.

Answer 1

Don't store times as UNIX timestamp, but instead as TIMESTAMP. Then you can simply set time_zone and everything will be converted as you wish:

SET time_zone = '+10:00';

SELECT COUNT(*) AS total
FROM   track
WHERE  date > NOW() - INTERVAL 2 DAY
   AND date < NOW() - INTERVAL 1 DAY

Otherwise, you can use CONVERT_TZ():

SELECT COUNT(*) AS total
FROM   track
WHERE  date > UNIX_TIMESTAMP(CONVERT_TZ(NOW() - INTERVAL 2 DAY, '+3:00', '+10:00'))
   AND date < UNIX_TIMESTAMP(CONVERT_TZ(NOW() - INTERVAL 1 DAY, '+3:00', '+10:00'))