php - Mysql时区和从一天中选择行

Question

我使用 MySQL DATETIME 列来存储日期和时间。日期为 UTC。我想从一天中选择项目。我现在在做什么:

SELECT * FROM data WHERE DATE(CONVERT_TZ(datetime, 'UTC', 'Australia/Sydney')) = '2012-06-01'

注意时区取决于用户

问题是随着表格的增长它非常慢。有什么解决办法可以让它更快吗？

Answer 1

目前，您的查询必须计算数据库每一行的转换。您可能可以通过反过来转换来使事情变得更好，这样转换只发生一次(或者实际上发生两次，因为您必须形成一个范围)。然后 datetime 上的适当索引应该会使事情变得非常快。

SELECT * FROM data
WHERE datetime BETWEEN CONVERT_TZ('2012-06-01 00:00:00', 'Australia/Sydney', 'UTC')
                   AND CONVERT_TZ('2012-06-01 23:59:59', 'Australia/Sydney', 'UTC')

或者，如果您担心 23:60:00 闰秒无法与任何查询匹配，您可以这样做

SELECT * FROM data
WHERE datetime >= CONVERT_TZ('2012-06-01', 'Australia/Sydney', 'UTC')
  AND datetime < CONVERT_TZ('2012-06-01' + INTERVAL 1 DAY, 'Australia/Sydney', 'UTC')

在后一种形式中，您不必在 PHP 端添加小时数，而是可以简单地将日期作为字符串参数传递。