假设我的数据库中有人是在-73440000 unix出生的。这意味着他出生在04-1967年9月-1967年UTC,但03-9月-1967年东部时间。在美国东部时间(),每年出生的人数是多少?
一开始,你就会发现
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之间的时差,因为这可能会在全年内发生变化(夏时制)。
发布于 2016-01-26 02:22:27
您可以手动进行转换;如果已经将区域数据加载到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子句中删除WHERE并将其添加到ORDER BY子句中,就像您在注释中建议的那样。
https://stackoverflow.com/questions/34933993
复制相似问题