PHP中使用SQLITE如何适配时区对Unix时间戳记录按日期分组

问题根因 #

PHP的date_default_timezone_set仅作用于PHP自身的时间处理函数，对SQLite的时间计算逻辑完全不生效。SQLite的localtime修饰符读取的是服务器操作系统的时区配置，当系统时区和你需要的GMT+8时区不一致时，就会出现日期拆分错误，你遇到的早6点前记录归入前一天的问题，本质就是时区不匹配导致的。

解决方法 #

直接在SQLite的日期函数中手动指定时区偏移，不再依赖不可控的localtime参数即可，把查询语句修改如下：

SELECT DATE([datetime],'unixepoch', '+8 hours') AS [date], COUNT (*) AS [number],  SUM([duration]) AS [ds], MAX ([peak]) AS [pm], SUM ([total]) AS [ts] from $table WHERE datetime >= $period_start and datetime <= $period_end GROUP BY [date]

注意事项 #

• 后续如果需要切换时区，只要把+8 hours改成对应偏移即可，比如GMT+7就写+7 hours，完全不受系统时区变动影响。
• 查询条件里的$period_start和$period_end要保证是GMT+8时区下对应时间的Unix时间戳，避免查询范围出错。比如要查询7月3日的所有记录时，$period_start应该是GMT+8时区下2021-07-03 00:00:00对应的时间戳，可以用PHP的strtotime("2021-07-03 00:00:00 Asia/Makassar")生成。

内容的提问来源于stack exchange，提问作者JavaLatte