mysql HOUR()计算时区

Question

我有一个查询，它返回按小时分组的结果，就像这样。

SELECT COUNT(*) FROM (`shipped_products`) WHERE HOUR(timestamp) = 8

问题是，我在本地主机上得到的结果与在实时服务器上得到的不同。两者的数据完全相同（UTC）。我相信我的本地主机数据库是纽约，而实时数据库是UTC，因此，HOUR（）在这两种环境中的工作方式不同。我没有更改实时服务器时区的权限，并且希望避免更改本地数据库时区。查询内部是否有方法确保HOUR（）函数无论数据库时区如何都返回相同的结果？

Answer 1

尝试CONVERT_TZ()函数：

SELECT COUNT(*) FROM (`shipped_products`)
WHERE HOUR(CONVERT_TZ(timestamp, 'UTC', 'America/New York')) = 8

手册警告：

备注

要使用命名时区（如'MET'或'Europe/Moscow'），必须正确设置时区表。有关说明，请参阅第10.6节"MySQL Server时区支持"。

您也可以使用小时值，但会丢失任何时区知识，如历史时区变化、夏令时等。

SELECT COUNT(*) FROM (`shipped_products`)
WHERE HOUR(CONVERT_TZ(timestamp, '+00:00', '-05:00')) = 8

Answer 2

您可以将会话时区（或全球时区）设置为UTC，如文档中所述。

set time_zone = '+00:00';

然后HOUR和其他函数将在UTC中解释timestamp类型。另请参阅本部分文档中timestamp和datetime之间的差异。

然而，另一个问题是WHERE HOUR(timestamp) = 8会使查询不可搜索，因此它将不得不扫描整个表，并且无法利用您可能拥有的任何索引。表中的记录越多，速度就越慢。（尤其是如果你有成千上万的记录）。

更好的方法是将其重写为范围查询。获取要筛选的开始和停止时间，并使用半开放间隔。

WHERE timestamp >= @start AND timestamp < @end

当然，在计算开始值和结束值时，您会将值锁定到特定日期。相反，如果您确实希望每天的UTC时间为8:00，那么您应该在表上创建一个包含该信息的单独整数列，以便在可搜索查询中使用它。

WHERE your_utc_hour_column = 8

在某些数据库中，这可能是一个计算列，但我认为MySQL不支持这些列，所以您必须手动创建和填充它。

Answer 3

虽然Matt Johnson的答案是正确的，miken32在大多数情况下都有正确的答案，但如果您发现自己无法更改数据库权限、无法上传时区表、无法执行SET语句，并且需要在夏令时更改时不会中断的东西，以下方法将起作用。

$corrected_hour = $hour;
if ($_SERVER['HTTP_HOST'] != 'localhost') 
{
    $dateTimeZoneNY = new DateTimeZone('America/New_York');
    $dateTimeZoneUTC = new DateTimeZone('UTC');
    $dateTimeUTC = new DateTime('now', $dateTimeZoneUTC);
    $timeOffset = $dateTimeZoneNY->getOffset($dateTimeUTC);
    $corrected_hour = $hour + abs($timeOffset) / 60 / 60;
}

只要两个数据库都保持其当前时区设置，这就不会中断。