mysql中处理时间偏移的建议
将这个项目简化为最简单的术语；
- 用户单击一个按钮，就会生成一条时间戳为 NOW() 的记录。
- NOW() 当然等于服务器上记录创建的时间。
- 我需要根据他们的时区而不是我的时区向他们显示统计数据。
在 MySql 中取消时区偏移的最佳方法是什么？ 是否有专门用于处理偏移量的特定字段格式？
我需要按照以下方式运行：
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
我不想询问用户他们所在的时区，我认为 JS 是从浏览器中提取这些数据的唯一方法。 也许如果它们在移动设备上，并且这不是基于网络的应用程序，我可以将其到达那里，但这可能不是它的方向。
我正在考虑最好的方法可能是确定它们的偏移量，并设置“server_time”+/- their_offset 的变量。 这使得服务器看起来好像位于不同的位置。 我相信这将是最好的，因为我不需要在代码中添加额外的 +/- 逻辑，从而使代码变得混乱并使其丑陋。
另一方面，这会将数据放入数据库中，并带有全面的时间戳。
建议？
Distilling this project down to the simplest of terms;
- Users click a button, a record is made with a timestamp of NOW().
- NOW() of course equals the time on the server of record creation.
- 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?
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.
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.
On the other hand, that puts the data in the database with time stamps that are all over the board.
Suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用 javascript 从客户端获取时区，如下所示：
var timeZone=(new Date().gettimezoneOffset()/60)*(-1);
打印变量并在使用之前进行测试。 我认为这将是你最简单的选择。
You can use javascript to get timezone from client as follows:
var timeZone=(new Date().gettimezoneOffset()/60)*(-1);
print the variable out and test before using it. I think this will be your simplest bet.
除了使用 JS 之外，您还可以获取 IP 地址的时区（使用 ip2location 等），然后使用 MySQL 的 CONVERT_TZ() 函数。
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.