如何在Laravel中将默认IST时区的datetime转换为用户指定时区并查询对应最值

现有代码的逻辑是先基于数据库默认的IST时区做日期筛选、分组，再转换时间到用户时区，统计出来的最值自然是IST时区维度的结果，不符合需求。正确逻辑是先把存储的IST时间转换为用户指定时区的时间，再基于转换后的时间做筛选、分组、最值统计。

// 预设参数说明
$dbTz = 'Asia/Kolkata'; // 数据库存储的时区
$usrTz = 'America/New_York'; // 用户指定时区
$fromTzTime = '2024-01-01'; // 用户时区维度的查询开始日期
$toTzTime = '2024-01-31'; // 用户时区维度的查询结束日期
$arrayDeviceID = [...]; // 要查询的设备ID数组
$devicesArr = [...]; // 设备数组

$sensor_data = DB::table('devices_sensor_data as D')
    ->select(
        'D.id',
        DB::raw('COALESCE(D.DeviceId,dx.DeviceId) AS DeviceId'),
        'D.ENERGY_Total',
        'D.Time',
        // 可选：把返回的时间也转成用户时区展示
        DB::raw('CONVERT_TZ(D.Time, ?, ?) AS Time_user_tz', [$dbTz, $usrTz])
    )
    ->join(DB::raw('
        (SELECT
            MIN(Time) min_time,
            MAX(Time) max_time,
            DeviceId
        FROM devices_sensor_data
        WHERE 
            DATE(CONVERT_TZ(Time, ?, ?)) BETWEEN ? AND ?
            AND DeviceId IN (?)
        GROUP BY DATE(CONVERT_TZ(Time, ?, ?)), DeviceId
        ) AS dx'
    ),
    function($join) use ($dbTz, $usrTz, $fromTzTime, $toTzTime, $arrayDeviceID) {
        // 绑定子查询参数，避免SQL注入
        $join->addBinding([$dbTz, $usrTz, $fromTzTime, $toTzTime, implode(',', $arrayDeviceID), $dbTz, $usrTz], 'join');
        $join->on(function ($on) {
            $on->on('D.Time', '=', 'dx.min_time')
               ->orOn('D.Time', '=', 'dx.max_time');
        });
        $join->on('D.DeviceId', '=', 'dx.DeviceId');
    })
    ->whereIn('D.DeviceId', array_keys($devicesArr))
    // 外层筛选也保留，缩小查询范围提升性能
    ->where(DB::raw('DATE(CONVERT_TZ(D.Time, ?, ?))', [$dbTz, $usrTz]), '>=', $fromTzTime)
    ->where(DB::raw('DATE(CONVERT_TZ(D.Time, ?, ?))', [$dbTz, $usrTz]), '<=', $toTzTime)
    ->orderBy('D.DeviceId')
    ->orderBy('D.Time')
    ->get();

• 所有日期筛选、分组逻辑都基于CONVERT_TZ(Time, 数据库时区, 用户时区)转换后的结果处理，保证统计维度是用户指定时区
• 子查询中直接统计原始时间字段的最值，用于和主表的原始时间字段匹配，保证关联正确性
• 替换了原SQL直接拼接变量的写法，改用参数绑定，避免SQL注入风险
• 修正了原JOIN条件的语法错误，避免逻辑歧义

如果MySQL执行CONVERT_TZ返回NULL，说明MySQL没有加载时区表，可通过以下指令临时加载（生产环境建议配置为开机自动加载）：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

内容的提问来源于stack exchange，提问作者Anita Mourya