如何将默认时区（IST）的日期时间转换为不同基于用户的时区？

Question

我有一个 PHP Laravel 的查询:

$sensor_data = DB::table('devices_sensor_data as D')
                ->select(DB::raw('
                    D.id,
                    COALESCE(D.DeviceId,dx.DeviceId) AS DeviceId,
                    D.ENERGY_Total,
                    D.Time')
                )

                ->join(DB::raw('
                    (SELECT
                        MIN(CONVERT_TZ(Time, "'.$dbTz.'", "'.$usrTz.'")) min_time,
                        MAX(CONVERT_TZ(Time, "'.$dbTz.'", "'.$usrTz.'")) max_time,
                        DeviceId
                    FROM devices_sensor_data
                    WHERE DATE(Time) BETWEEN "'.$fromTzTime.'" AND "'.$toTzTime.'"
                    AND DeviceId IN (\''.$arrayDeviceID.'\')
                    GROUP BY DATE(Time), DeviceId ORDER BY DATE(Time)
                    ) AS dx'
                ),
                function($join)
                {
                    $join->on(DB::raw('D.Time = `dx`.`min_time` OR D.Time'), '=', 'dx.max_time');
                    $join->where('D.DeviceId', '=', DB::raw('dx.DeviceId'));
                })
                ->whereIn('D.DeviceId', array_keys($devicesArr))
                ->whereDate('D.Time', '>=', $fromTzTime)
                ->whereDate('D.Time', '<=', $toTzTime);

                $sensor_data = $sensor_data
                ->orderBy('D.DeviceId')
                ->orderBy('D.Time')
                ->get();

我希望根据不同的用户所在时区选择MIN和MAX，而不是默认值，目前的默认值是Asia/Kolkata，因此我想根据例如America/New_York来选择它。

它按照IST时区返回给我最小值和最大值，只需将其转换为纽约时间即可，但我想根据纽约时区获取最小值和最大值。

- Anita Mourya

---

请[编辑]您的问题以澄清您的问题。您可能需要使用CONVERT_TZ(datestamp，'Asia / Kolkata'，'Asia / Dhaka')来转换您的时间。或者是CONVERT_TZ(datestamp，'Asia / Kolkata'，'Europe / London')？ - O. Jones

你好@O.Jones，这是我写的如何在Mix Max中使用convert_tz()函数？ 它给了我语法错误。 - Anita Mourya

你是否按照 https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html 中所述创建了时区表？如果没有，那么你无能为力。 - Salman A

该列是否声明为DATETIME或TIMESTAMP？客户端的系统时间设置为正确的时区了吗？ - Rick James

Answer 1

如果你想在 CONVERT_TZ 函数中使用命名时区，你必须创建、填充和维护时区系统表。一旦表格设置完成，你可以像这样使用该函数：

SELECT CONVERT_TZ('2021-10-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 2021-10-01 13:00:00
-- British territories use BST in summer, where it is 4:30 hours behind IST

SELECT CONVERT_TZ('2021-11-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 2021-11-01 12:00:00
-- Outside of summer, these territories use GMT which is 5:30 hours behind IST

如果时区表为空，该函数将返回 null。仅支持很少的时区缩写（例如 UTC 和 GMT），因此您必须使用城市名称。

对于您的特定示例，只需计算最小/最大值，然后进行转换：

SELECT CONVERT_TZ(MIN(...), 'Asia/Kolkata', 'Europe/London')

编辑

由于问题还标记有php，我想补充一下，使用DateTime类可以在PHP中进行转换。时区支持已内置于PHP中。因此，如果您拥有已知格式的日期字符串并且知道它所在的时区，则可以进行如下转换：

$date = DateTime::createFromFormat('Y-m-d H:i:s', '2021-10-01 17:30:00', new DateTimeZone('Asia/Kolkata'));
$date->setTimezone(new DateTimeZone('Europe/London'));
echo $date->format('Y-m-d H:i:s');
// 2021-10-01 13:00:00

$date = DateTime::createFromFormat('Y-m-d H:i:s', '2021-11-01 17:30:00', new DateTimeZone('Asia/Kolkata'));
$date->setTimezone(new DateTimeZone('Europe/London'));
echo $date->format('Y-m-d H:i:s');
// 2021-11-01 12:00:00

- Salman A

Answer 2

你确实需要使用CONVERT_TZ函数。让我创建一个带有数据的样例数据库：

create table MyTimes(
  id int auto_increment primary key,
  moment timestamp
);

insert into MyTimes(moment) values
('2021-01-02 00:00:00'),
('2021-01-01 00:00:00'),
('2021-01-04 00:00:00'),
('2021-01-03 00:00:00');

现在让我们使用时区转换select最小值和最大值：

select min(convert_tz(moment, '+05:30', '+01:00')) minimum, max(convert_tz(moment, '+05:30', '+01:00')) maximum
from MyTimes;

示例：http://sqlfiddle.com/#!9/cfca54f/4

现在，让我们看一下这个示例中发生了什么：

• 我们进行选择操作
• 返回一个包含时区转换的最小和最大时间的聚合数据记录
• 使用convert_tz函数实现
• 将moment作为第一个参数
• 当前时区作为第二个参数
• 目标时区作为第三个参数

- Lajos Arpad

---

BST现在没有被使用...这是一个“夏令时”。两周前使用此时间的国家现在正在使用GMT (+00:00)。 - Salman A

Answer 3

更长远的解决方案是...

仔细选择 DATETIME 和 TIMESTAMP。两者都可以保存日期和时间。

将 DATETIME 视为时钟图片。当不同时区的用户读取此列时，他们会看到您看到的内容，不会进行时区调整。

请注意，如果您所在的时区使用夏令时，则 DATETIME 每年有两次小问题。

将 TIMESTAMP 视为存储时转换为UTC，然后在读取时转换回客户端时区的时间点。或将其视为宇宙中的一个时间点。这对于公布 Zoom 会议时间等活动非常有效。

要使其正常工作（使用 TIMESTAMP），每台客户机都必须配置为“本地”时区。

（还有其他需要调整时间的情况；MySQL 只有上述两种情况。）

- Rick James