假设我的数据库中有人出生-73440000
Unix时代。这意味着他出生于 UTC 时间 1967 年 9 月 4 日,但美国东部时间 1967 年 9 月 3 日。我将如何计算EDT中一年中每天出生的人数?
马上,你会发现
SELECT FROM_UNIXTIME(-73440000)
-- returns NULL
返回NULL
.MySQL无法处理负的unix时间戳。
很好,我们可以解决这个问题:
select date_add('1970-01-01', interval -73440000 second)
-- returns 1967-09-04 00:00:00
产生1967-09-04 00:00:00
这是他在UTC中的出生日期。
我们可以尝试将其转换为EDT(多伦多时间):
select convert_tz('1967-09-04 00:00:00','UTC','America/Toronto')
-- returns 1967-09-04 00:00:00, but should be 1967-09-03 20:00:00
但事实证明,CONVERT_TZ
在 1970 年之前的日期也不起作用。
(我已经安装了时区,它确实适用于 1970 年至 2038 年之间的日期)
所以现在我被困住了。我需要将 unix 时间戳转换为 MySQL 日期,以便我可以按它分组然后对其进行计数。我能想到的唯一另一种选择是将数据库中的每条记录作为 unix 时间戳返回,并使用另一种语言进行转换并统计它们,但如果有数百万条记录,这可能会变得有点荒谬。
注:注:您也无法计算 EDT 和 UTC 之间的小时偏移量,因为这可能会在一年中发生变化(夏令时)。
您可以手动进行转换;如果您已经将区域数据加载到MySQL中,那么您已经获得了可以追溯到1918年的数据。让我们看一下列表:
SET @timezone = "America/Toronto";
SELECT FROM_UNIXTIME(0) + INTERVAL Transition_Time SECOND AS change_time, Offset, Abbreviation
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt ON (
t.Time_zone_id = tt.Time_zone_id AND
t.Transition_type_id = tt.Transition_type_id
)
LEFT JOIN mysql.time_zone_name n ON (
t.Time_zone_id = n.Time_zone_id
)
WHERE n.Name = @timezone
AND Transition_time < 0
ORDER BY change_time ASC;
修改您的样品日期,我们可以提取以下内容:
SET @timezone = "America/Toronto";
SET @birthday = -73440000;
SELECT ('1970-01-01 00:00:00' + INTERVAL @birthday SECOND) + INTERVAL Offset SECOND AS offsetDate
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt ON (
t.Time_zone_id = tt.Time_zone_id AND
t.Transition_type_id = tt.Transition_type_id
)
LEFT JOIN mysql.time_zone_name n ON (
t.Time_zone_id = n.Time_zone_id
)
WHERE n.Name = @timezone
AND Transition_time < @birthday
ORDER BY Transition_time DESC
LIMIT 1;
由于它选择日期之前的最新转换,因此不会返回第一个转换之前的日期的记录。这应该作为一个指标,表明你处于未知领域,必须弥补你自己的偏移量。
如果要使用第一个转换时间作为后备,则可以从WHERE
子句中删除Transition_time < @birthday
,并将其添加到ORDER BY
子句中,如注释中所示。
我已经用
将时间戳转换为日期时间:
CREATE FUNCTION `ts2dt` (`d` bigint) RETURNS datetime
DETERMINISTIC
BEGIN
DECLARE `result` DATETIME;
SELECT ('1970-01-01 00:00:00' + INTERVAL `d` SECOND) + INTERVAL Offset SECOND INTO `result`
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt ON (
t.Time_zone_id = tt.Time_zone_id AND
t.Transition_type_id = tt.Transition_type_id
)
LEFT JOIN mysql.time_zone_name n ON (
t.Time_zone_id = n.Time_zone_id
)
WHERE n.Name = @@session.time_zone
ORDER BY Transition_time <= `d` DESC, Transition_time DESC
LIMIT 1;
RETURN `result`;
END
将日期时间转换为时间戳:
CREATE FUNCTION `dt2ts` (`d` DATETIME) RETURNS BIGINT
DETERMINISTIC
BEGIN
DECLARE `result` BIGINT;
SELECT TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `d`) - Offset INTO `result`
FROM mysql.time_zone_transition t
LEFT JOIN mysql.time_zone_transition_type tt
ON t.Time_zone_id = tt.Time_zone_id AND t.Transition_type_id = tt.Transition_type_id
LEFT JOIN mysql.time_zone_name n ON t.Time_zone_id = n.Time_zone_id
WHERE n.Name = @@session.time_zone
ORDER BY Transition_time <= TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `d`) - Offset DESC, Transition_time DESC
LIMIT 1;
RETURN `result`;
END
我发现这和 PHP 之间的唯一区别是dt2ts('2016-11-06 01:00:00')
给出了America/Vancouver
1478422800
,而 PHP 给出了1478419200
,但是,两个时间戳都对应于凌晨 1:00(因为 DST 开关),所以我想任何一个答案都是正确的。
:注:如果未设置会话时区,这些函数会提供null
。设置它:
set session time_zone='America/Vancouver';