如何使用convert_tz或任何其他函数优化数据库时区
在处理时区时，我正在尝试优化我的mySQL查询。我的数据库(mySQL)被设置为EET time(+02:00) (我很快将在AWS上使用UTC)，但是在任何情况下，我们的Cakephp实现都有一个作为UTC检索记录的设置。我们的timestamp列是timestamp类型。
因此，在我们的2019-12-19 12:44:27 (+2)中发现的mySQL实际上是CakePHP实现中的2019-12-19 10:44:27 (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谢谢
回答 6
Stack Overflow用户
发布于 2019-12-31 01:42:07
来自MySQL documentation about the TIMESTAMP type
MySQL将当前时区的
TIMESTAMP值转换为UTC存储，并将从UTC转换回当前时区进行检索。(其他类型(如DATETIME)不会出现这种情况。默认情况下，每个连接的当前时区是服务器的时间。时区可以按每个连接设置.
因此，您实际上是在保存基于UTC的值。您可能认为您存储了UTC+2值，但这仅仅是因为查询时的默认时区(会话时区)与服务器的默认时区相同。
此外，像NOW()这样的函数也使用会话时区。因此，由于会话时区在双方都是相同的，因此不需要进行时区转换。你只需说：
AND timestamp >= NOW()这有一个额外的好处(正如symcbean's answer所指出的)，即允许DBMS使用索引-换句话说，查询变成sargable。
您还可能希望阅读MySql文档中的优秀文章，该文章解释了会话时区如何影响索引查询和非索引查询。
在查询时将时区显式地设置为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)Stack Overflow用户
发布于 2020-01-03 07:41:19
我对你的问题有两个解决办法，希望其中一个对你有用。
解决方案1
在timeZone中更改datetime的cakePHP，然后执行查询。
public static function convertDate($datetime, $companyTimeZone, $dbTimeZone)
{
$newDate = new DateTime($datetime, new DateTimeZone($companyTimeZone));
$newDate->setTimezone(new DateTimeZone($dbTimeZone));
return $newDate->format('Y-m-d H:i:s');
}
$datetime = self::convertDate($datetime, $companyTimeZone, $dbTimeZone);
SELECT company_id, COUNT( timestamp ) AS views, url
FROM behaviour
WHERE company_id = 1
AND timestamp >= $datetime
GROUP BY URL
ORDER BY views
DESC LIMIT 20解决方案2
您只能为当前会话设置mysql的timeZone。
##$company_timezone ='+04:00';
#NOTE this will set timZone only for the current session
SET time_zone= $company_timezone;
SELECT company_id, COUNT( timestamp ) AS views, url
FROM behaviour
WHERE company_id = 1
AND timestamp >= NOW()
GROUP BY URL
ORDER BY views
DESC LIMIT 20备注：我的应用程序的DB是UTC，用户来自世界各地，所以我已经实现了解决方案1，它从2年以来一直运行良好。
Stack Overflow用户
发布于 2019-12-31 01:07:51
您对谓词的两边都应用了相同的转换--所有这一切都使得DBMS无法使用索引。直接比较这些值：
AND timestamp >= NOW()如果您要将一个时区中的数据与另一个时区中的挂钟进行比较，这会变得更加复杂--但您只需要确保只将转换应用到谓词的一侧，该谓词只包含文字，而不包含属性引用。
https://stackoverflow.com/questions/59408715
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号