PHP 和mysql记录-个人时区
我有一个小但令人讨厌的问题：
我有一个包含许多记录的数据库，每个记录都有两个带有 mysql 时间戳的字段，如下所示： 2012-02-14 11:09:59
并且由于每一行都有它自己的时区（我将时区保存为字符串，例如“欧洲/柏林”，也作为偏移量，如浮点字段中的+1），我有一点问题：
我想查询数据库中比 -now- 更新或更旧的记录 我可以将日期转换为 GMT 并使用服务器 tmz-offset 搜索它们。 简单，但是，麻烦的是，我希望能够搜索在某个当地时间发生的记录，例如：
我想搜索世界各地明天 22:00 发生的一件事。 现在，如果我将所有时间戳转换为 GMT，我就无法这样做，因为在 where 条件下 22:00 不会是当地时间，而是 GMT 时间... 有什么方法可以将时间戳和 tmz 字段合并到一个查询中吗？
提前感谢1000！
I have a little, but nasty problem:
I have a database with many records, every record has two fields with mysql timestamps, like this:
2012-02-14 11:09:59
and since every row has it's individual timezone (i saved the timezone as string, such as "Europe/Berlin" and also as offset like +1 in a float field), i have a little problem:
I want to query the db for records that are newer or older than -now-
I could convert the date to GMT and search them with the servers tmz-offset.
Easy, buuuuttt, the trouble is, i want to have the possibility to search for records that happen at a certain local time, like for instance:
I want to search for a thing that happens at 22:00 tomorrow all over the world.
Now if i convert all timestamps to GMT i could not do that, because in the where condition 22:00 is not going to be the local time, but the GMT-time...
Is there any way i could combine the timestamp and the tmz field in one query?
Thanks 1000 in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为理想的解决方案是
将所有时间存储在UTC中，并在输出日期时进行偏移计算
或者有一个第二个单独的列包含 UTC 日期。
但是，您最初的要求应该也是可能的 - 使用
DATE_ADD()来计算正确的偏移量：I think the ideal solution would be either to
Store all times in UTC and do the offset calculations when outputting the date
or have a second, separate column with the UTC date.
However, your original requirement should be possible as well - use
DATE_ADD()to calculate the correct offset: