如何在MySQL和PHP中按用户时区获取日期时间聚合数据

SELECT CONVERT_TZ('2021-10-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 结果: 2021-10-01 13:00:00 (夏令时，比IST晚4小时30分)

SELECT CONVERT_TZ('2021-11-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 结果: 2021-11-01 12:00:00 (非夏令时，比IST晚5小时30分)

SELECT
    D.id,
    COALESCE(D.DeviceId, dx.DeviceId) AS DeviceId,
    D.ENERGY_Total,
    -- 将原始时间转换为用户时区后显示
    CONVERT_TZ(D.Time, 'Asia/Kolkata', 'America/New_York') AS TimeInUserTz
FROM
    devices_sensor_data AS D
JOIN
    (
        SELECT
            -- 在用户时区中计算MIN和MAX
            MIN(CONVERT_TZ(Time, 'Asia/Kolkata', 'America/New_York')) AS min_time_user_tz,
            MAX(CONVERT_TZ(Time, 'Asia/Kolkata', 'America/New_York')) AS max_time_user_tz,
            DeviceId
        FROM
            devices_sensor_data
        WHERE
            -- 过滤条件也基于用户时区
            DATE(CONVERT_TZ(Time, 'Asia/Kolkata', 'America/New_York')) BETWEEN '2021-10-01' AND '2021-10-01'
            AND DeviceId IN ('device_id_1', 'device_id_2')
        GROUP BY
            -- 分组条件也基于用户时区
            DATE(CONVERT_TZ(Time, 'Asia/Kolkata', 'America/New_York')), DeviceId
    ) AS dx ON (
        -- 连接条件需要考虑原始时间与转换后的时间
        D.Time = CONVERT_TZ(dx.min_time_user_tz, 'America/New_York', 'Asia/Kolkata')
        OR D.Time = CONVERT_TZ(dx.max_time_user_tz, 'America/New_York', 'Asia/Kolkata')
    )
WHERE
    D.DeviceId IN ('device_id_1', 'device_id_2')
    -- 主查询的过滤条件也应基于用户时区
    AND DATE(CONVERT_TZ(D.Time, 'Asia/Kolkata', 'America/New_York')) >= '2021-10-01'
    AND DATE(CONVERT_TZ(D.Time, 'Asia/Kolkata', 'America/New_York')) <= '2021-10-01'
ORDER BY
    D.DeviceId, D.Time;

<?php

// 原始日期时间字符串及其所属时区
$originalDateTimeStr = '2021-10-01 17:30:00';
$sourceTimeZone = new DateTimeZone('Asia/Kolkata');

// 创建DateTime对象，指定原始日期时间字符串、格式和原始时区
$date = DateTime::createFromFormat('Y-m-d H:i:s', $originalDateTimeStr, $sourceTimeZone);

if ($date === false) {
    echo "日期解析失败。\n";
    exit;
}

// 设置目标时区
$targetTimeZone = new DateTimeZone('Europe/London');
$date->setTimezone($targetTimeZone);

// 输出转换后的日期时间
echo $date->format('Y-m-d H:i:s') . "\n";
// 结果: 2021-10-01 13:00:00

// 另一个示例（非夏令时）
$originalDateTimeStr2 = '2021-11-01 17:30:00';
$date2 = DateTime::createFromFormat('Y-m-d H:i:s', $originalDateTimeStr2, $sourceTimeZone);

if ($date2 === false) {
    echo "日期解析失败。\n";
    exit;
}

$date2->setTimezone($targetTimeZone);
echo $date2->format('Y-m-d H:i:s') . "\n";
// 结果: 2021-11-01 12:00:00

?>