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

问题描述：

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.

我使用 p>

  SELECT成功获取昨天的mySQL数据 COUNT（*）作为总轨道数
WHERE FROM_UNIXTIME（日期，'％Y-％m-％d％h：％m：％s'）＆gt;  DATE_ADD（NOW（），INTERVAL -2 DAY）
AND FROM_UNIXTIME（日期，'％Y-％m-％d％h：％m：％s'）＆lt;  DATE_ADD（NOW（），INTERVAL -1 DAY）
  code>  pre> 
 
 但是，它使用服务器的时区。 我的服务器位于美国，如果访客来自与美国不同的时区（例如：亚洲或欧洲），我的昨天数据对用户来说是不正确的。 我想根据访问者的时区获取正确的昨天结果。 我可以在php中获取访问者时区，但我无法弄清楚如何在mySQL中使用它。 p> 
  div>

答

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'))

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

相关推荐