Laravel应用MySQL按America/Los_Angeles时区分组数据起始异常

我有一个为Laravel/PHP应用存储数据的数据库，所有数据均以UTC时区存储。我正在编写查询语句，按日期和小时分组数据以用于图表展示，UI需以America/Los_Angeles时区展示报表数据。

我需要获取该时区下过去3天的数据，通过以下代码获取BETWEEN的时间范围：

$from = now()
    ->startOfDay()
    ->subDays(3)
    ->timezone('America/Los_Angeles');

$to = now()
    ->endOfDay()
    ->timezone('America/Los_Angeles');

这部分运行正常。接下来我需要按America/Los_Angeles时区而非UTC分组数据，使用了如下SQL语句：

SELECT
  DATE(CONVERT_TZ(created_at, '+00:00', '-07:00')) AS grouped_date,
  HOUR(CONVERT_TZ(created_at, '+00:00', '-07:00')) AS grouped_hour,
  count(*) AS requests
FROM
  `advert_requests`
WHERE
  created_at BETWEEN '2022-09-09 17:00:00' AND '2022-09-13 16:59:59'
GROUP BY
  grouped_date,
  grouped_hour

但出现异常：分组数据起始于2022-09-09 10:00，而按逻辑，BETWEEN起始时间2022-09-09 17:00:00（America/Los_Angeles时区）对应UTC的2022-09-10 00:00:00，转换后分组数据应从0点开始，请问原因是什么？

核心错误点 #

1. WHERE条件时间范围不匹配
   你直接将America/Los_Angeles时区的时间字符串传入WHERE子句，和数据库中UTC存储的created_at字段比较。数据库会把这些字符串解析为UTC时间，导致查询范围错误：

• 你预期的起始时间是LA时区的2022-09-09 17:00:00，对应UTC的2022-09-10 00:00:00
• 但实际查询时，数据库把2022-09-09 17:00:00当作UTC时间，会包含所有created_at >= UTC 2022-09-09 17:00:00的数据，这些数据转成LA时区就是2022-09-09 10:00:00，这就是分组起始于10点的原因。

2. 硬编码时区偏移不可靠
   直接使用-07:00作为偏移量，无法处理夏令时切换（America/Los_Angeles时区在夏令时是UTC-7，冬令时是UTC-8），长期来看会导致时区转换错误。

正确实现方式 #

1. 修正查询时间范围

先计算LA时区的时间范围，再转换为UTC时间传入数据库查询：

// 计算LA时区的时间范围
$laFrom = now()
    ->startOfDay()
    ->subDays(3)
    ->timezone('America/Los_Angeles');
$laTo = now()
    ->endOfDay()
    ->timezone('America/Los_Angeles');

// 转换为UTC时间，适配数据库存储
$fromUtc = $laFrom->copy()->timezone('UTC');
$toUtc = $laTo->copy()->timezone('UTC');

2. 正确转换时区分组

使用时区名称而非固定偏移，让数据库自动处理夏令时：

SELECT
  DATE(CONVERT_TZ(created_at, 'UTC', 'America/Los_Angeles')) AS grouped_date,
  HOUR(CONVERT_TZ(created_at, 'UTC', 'America/Los_Angeles')) AS grouped_hour,
  count(*) AS requests
FROM
  `advert_requests`
WHERE
  created_at BETWEEN ? AND ? -- 绑定$fromUtc和$toUtc的UTC时间
GROUP BY
  grouped_date,
  grouped_hour

Laravel Query Builder写法（更安全）

use App\Models\AdvertRequest;

$results = AdvertRequest::query()
    ->selectRaw("DATE(CONVERT_TZ(created_at, 'UTC', 'America/Los_Angeles')) AS grouped_date")
    ->selectRaw("HOUR(CONVERT_TZ(created_at, 'UTC', 'America/Los_Angeles')) AS grouped_hour")
    ->selectRaw('count(*) AS requests')
    ->whereBetween('created_at', [$fromUtc, $toUtc])
    ->groupBy('grouped_date', 'grouped_hour')
    ->get();

这样就能保证查询的是LA时区过去3天对应的UTC数据，且分组时正确转换为LA时区的日期和小时，不会出现起始时间偏移的问题。

内容的提问来源于stack exchange，提问作者James Mills