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')

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

Answer 2

根据您的实际目标，使用TIMESTAMP而不是DATETIME可能是一个不错的解决方案。

TIMESTAMP将日期时间存储为UTC，在存储和提取时转换为本地时区。这样，我从你的表中读取的内容就会自动与你存储的内容不同（假设我们在不同的时区）。

是的，使用@MvG的翻转查询方法。是的，用他的第二种形式。

并为列编制索引。在复合索引中，将时间戳放在最后，尽管它更具选择性。

Answer 3

1. 不要选择*
2. 索引-确保使用适当的colunms/id字段被编入索引
3. 在php端做时间转换
4. 或者确保你做1&2，并且它可以被封装到存储过程中，将时区作为参数传递

Answer 4

当前MySQL查询如下：

SELECT * FROM data
WHERE datetime >= CONVERT_TZ('2012-06-01', '+00:00', '+10:00')
  AND datetime < CONVERT_TZ('2012-06-01' + INTERVAL 1 DAY, '+10:00', '+00:00')