php - 如何使用 convert_tz 或任何其他函数优化数据库时区

Question

我正在尝试在处理时区时优化我的 mySQL 查询。我的数据库（mySQL）设置为 EET 时间（+02:00）（我将很快转移到我将使用 UTC 的 AWS 上），但无论如何，我们的 Cakephp 实现具有将记录检索为 UTC 的设置。我们的timestamp专栏是他的一个timestamp类型。

因此，2019-12-19 12:44:27在我们的 mySQL (+2) 中找到的 a 实际上是2019-12-19 10:44:27CakePHP 实现中的 (UTC)。

问题是我需要显示日期范围之间的行，例如今天的结果，但根据公司的时区而不是根据服务器/数据库。

考虑到时区，我创建了以下查询+04:00。

$company_timezone ='+04:00';
SELECT company_id, COUNT( timestamp ) AS views, url 
FROM behaviour 
WHERE company_id = 1
AND CONVERT_TZ(timestamp,'+00:00','{$company_timezone}')  >= DATE(CONVERT_TZ(NOW(),'+00:00','{$company_timezone}')) 
GROUP BY URL 
ORDER BY views 
DESC LIMIT 20

然而，这在性能方面是非常需要的。大约需要 4-5 秒。没有convert_tz它需要不超过0.5秒。

我的问题是如何优化这个？当然，我们的timestamp列是索引的，即使在特定查询中也没有任何意义，因为我将它与convert_tz.

处理日期范围的新查询（今天 - 昨天）

我相信以下示例更强调了我的案例：使用此查询，我根据公司的时区显示结果。例如，如果他们公司的当地时间是 00:01，那么这对他们来说是新的一天，无论服务器/mysql 的时间如何。

SELECT COUNT(hash) as how_many
FROM   table          
WHERE  company_id = 1
AND CONVERT_TZ(last_visit,'+00:00','{$company_timezone}')  >= DATE(CONVERT_TZ(NOW(),'+00:00','{$company_timezone}') - INTERVAL 1 DAY)
GROUP BY date(last_visit)
ORDER BY last_visit DESC

谢谢

标签： phpmysqltimezone

Answer 1

从有关类型的 MySQL文档中TIMESTAMP：

MySQL 将TIMESTAMP值从当前时区转换为 UTC 进行存储，然后从 UTC 转换回当前时区进行检索。（这不会发生在其他类型，例如DATETIME.）默认情况下，每个连接的当前时区是服务器的时间。可以基于每个连接设置时区。

因此，您实际上是在保存基于 UTC 的值。您可能认为您已经存储了 UTC+2 值，但这只是因为查询时的默认时区（会话时区）与服务器的默认时区相同。

此外，类似的功能NOW()也使用会话时区。因此，由于双方的会话时区相同，因此无需进行时区转换。你可以说：

AND timestamp >= NOW()

这有额外的好处（正如symcbean 的回答所指出的），允许 DBMS 使用索引 - 换句话说，查询变得sargable。

您可能还想阅读 MySql 文档中的优秀文章Indexed Lookups from TIMESTAMP Columns，它解释了会话时区如何影响索引查询和非索引查询。

在查询时将时区显式设置为 UTC 也可能是值得的：

SET time_zone = 'UTC';

无论哪种方式，您都会得到相同的结果，但这会稍微高效一些，因为 DBMS 现在需要执行的时区转换更少。

---

对于您编辑中的第二个查询，您仍然在表达式的左侧显示转换。同样，您希望它只是字段，以便可以使用索引。由于该字段是一种TIMESTAMP类型，因此您只需计算要检索的最早时间戳即可。

如果您想在内联查询中执行此操作，转换次数会使其有点复杂：

SELECT COUNT(hash) as how_many
FROM   table          
WHERE  company_id = 1
  AND  last_visit >= CONVERT_TZ(DATE(CONVERT_TZ(NOW(), @@session.time_zone, '{$company_timezone}')) - INTERVAL 1 DAY, '{$company_timezone}', @@session.time_zone)
GROUP BY DATE(CONVERT_TZ(last_visit, @@session.time_zone, '{$company_timezone}'))
ORDER BY DATE(CONVERT_TZ(last_visit, @@session.time_zone, '{$company_timezone}'))

出于这个原因，在查询之前简单地更改会话时区要容易得多，这样它就可以统一应用于所有操作：

SET time_zone = '{$company_timezone}';
SELECT COUNT(hash) as how_many
FROM   table          
WHERE  company_id = 1
  AND  last_visit >= DATE(NOW()) - INTERVAL 1 DAY
GROUP BY DATE(last_visit)
ORDER BY DATE(last_visit)