关于在mysql中处理时间偏移的建议

Distilling this project down to the simplest of terms;

将该项目提炼为最简单的术语;

Users click a button, a record is made with a timestamp of NOW().

用户单击一个按钮,记录的时间戳为NOW()。

NOW() of course equals the time on the server of record creation.

NOW()当然等于创建记录服务器上的时间。

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?

在MySql中处理时区偏移的最佳方法是什么?是否存在旨在处理偏移的特定字段格式?

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.

我宁愿不问用户他们在哪个时区,我认为JS是将这些数据从浏览器中提取出来的唯一方法。也许如果他们在移动设备上,并且这不是基于网络的应用程序,我可以在那里得到它,但这可能不是这个方向。

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.

我正在考虑最好的方法是确定它们的偏移量,并将变量设置为“server_time”+/- their_offset。这使得服务器看起来好像位于不同的位置。我相信这是最好的,因为没有额外的+/-逻辑我需要添加到代码中,混淆它并使其变得丑陋。

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

另一方面,这会将数据放入数据库中,并带有全面的时间戳。

Suggestions?

2 个解决方案

#1

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.

除了使用JS之外,您可以获得其IP地址的时区(使用类似ip2location的东西),然后使用MySQL的CONVERT_TZ()函数。

#2

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

您可以使用javascript从客户端获取时区,如下所示:

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

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

在使用之前打印变量并进行测试。我想这将是你最简单的赌注。

#1