时区特定的“立即打开"功能 [英] Timezone specific "Open Now" function
问题描述
在我正在构建的网站上，我需要一个现在打开"的 php 函数.查看以前的问题，我有建立我的 mysql 表:
I need an 'is open now' php function on a site I am building. Looking at a previous question, I have built my mysql table:
hours_id INT NOT NULL,
loc_id INT NOT NULL,
dow TINYINT NOT NULL,
open_time TIME NOT NULL,
close_time TIME NOT NULL,
然后使用:
"SELECT open_time, close_time
FROM opening_hours
WHERE dow=dayofweek(curdate())
AND loc_id=:loc_id"
然后我可以确定它是否打开.我正在构建的产品是全球性的，我需要将立即打开"与位置所在的时区相关联，而不是服务器位置或查看者位置.
I can then work out if it is open or not. The product I am building is global and I will need the 'open now' to be related to the timezone the location is in, not the server location or the viewers location.
我已经将国家代码和位置的纬度/经度存储在相关的数据库中，所以我的想法是我从中获取时区，或者提供一种方法让用户选择一个，然后修改我的 SQL不知何故.
I am already storing the country code and lat/lng of the location in a related db, so my thoughts are that I obtain the timezone from that, or provide a method for the user to select one, and then modify my SQL somehow.
我的方向是否正确?你的方法是什么?
Am I heading in the right direction? What would your method be?
推荐答案
阅读时区支持MySQL，并确保您的 mysql 数据库配置了当前时区表.定期更新.
Read about time zone support in MySQL, and ensure your mysql database is configured with current time zone tables. Update regularly.
将每个位置与命名的 IANA/Olson 时区相关联，例如 "America/Los_Angeles" 或 "Europe/London".请参阅此处的列表.如果您有纬度/经度，您可以通过其中一种方法查找时区.
Associate each location with a named IANA/Olson time zone, such as "America/Los_Angeles", or "Europe/London". Refer to the list here. If you have lat/lon, you can look up the time zone via one of these methods.
使用 MySQL CONVERT_TZ 函数将当前 UTC 时间转换为特定区域.例如，CONVERT_TZ(UTC_TIMESTAMP(),'UTC','Asia/Tokyo')
使用转换时间的星期几和一天中的时间来检查位置条目中的日期和范围.
Use the day-of-week and time of day of the converted time to check against the day and range in the location's entry.
另外，请注意，其他人可能会建议采取仅将 UTC 存储在数据库中的方法，或者在与现在"值进行比较之前将所有值转换为 UTC.在极端情况下，这两种方法中的任何一种都可能失败，因为 UTC 星期几不一定与每个时区中的星期几相同.
Also, note that others may suggest taking the approach of only storing UTC in the database, or of converting all of the values to UTC before comparing against the "now" value. Either of those approaches can fail in edge cases, since the UTC day-of-week is not necessarily the same day-of-week as in each time zone.
另一种将有效但需要更多努力的方法是预先确定未来一段时间内的特定 UTC 开始和停止时间(至少到下一个，但也许更多).然后您可以使用 UTC 时间扫描此列表.当您有数千个或更多的单个条目要检查时，这在规模上效果更好.但在较小的规模上，通常不值得开销.
One other approach that will work, but takes more effort, is to predetermine the specific UTC starting and stopping times for some amount of time in the future (at least to the next one, but perhaps further). Then you can scan this list with the UTC time. This works better at scale, when you have thousands or more individual entries to check. But at smaller scales, it's usually not worth the overhead.
同样，您可以有一个后台进程，它只是在每条记录上设置一个现在打开"标志，但它必须不断地针对您的数据库工作，并且除了现在"之外，您永远无法检查其他时间.
Likewise, you could have a background process that just sets a "now open" flag on each record, but it would have to constantly be working against your database, and you could never check for other times than "now".
这篇关于时区特定的“立即打开"功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
