mysql– 当日期存储为unix时间戳时,如何计算美国东部时间3月9日19日出生的人数？

概述假设我的数据库中有人出现在-73440000 unix时间.这意味着他出生于1967年9月4日UTC,但美国东部时间19日至9月19日.我如何计算在美国东部时间每年每天出生的人数？马上,你会发现这一点SELECT FROM_UNIXTIME(-73440000) -- returns NULL 返回NULL. MySQL无法处理负的unix时间戳.好的,我们

假设我的数据库中有人出现在-73440000 unix时间.这意味着他出生于1967年9月4日UTC,但美国东部时间19日至9月19日.我如何计算在美国东部时间每年每天出生的人数？

马上,你会发现这一点

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年之前的日期也不起作用.

(我是installed the timezones already,它确实在1970年和2038之间的日期工作)

所以现在我被卡住了.我需要将unix时间戳转换为MysqL DATE,以便我可以GROUP BY它然后COUNT它.我能想到的唯一另一个选择是将数据库中的每条记录作为unix时间戳返回,并使用另一种语言进行转换并计算它们,但如果有数百万条记录,这可能会有点荒谬.

注：您无法计算EDT和UTC之间的小时偏差,因为这可能会在一年中发生变化(夏令时).

最佳答案你可以手动进行转换;如果你已经将区域数据加载到MysqL中,那么你的数据已经回到了1918年.让我们来看看列表：

SET @timezone = "America/Toronto";SELECT FROM_UNIXTIME(0) + INTERVAL Transition_Time SECOND AS change_time,Offset,AbbreviationFROM MysqL.time_zone_Transition tleft 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 < 0ORDER 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 offsetDateFROM MysqL.time_zone_Transition tleft 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 < @birthdayORDER BY Transition_time DESCliMIT 1;

由于它在日期之前选择最近的转换,因此它将不返回第一次转换之前的日期的记录.这应该作为一个指标,表明你处于未知状态并且必须弥补自己的偏移量.

如果您想将第一个转换时间用作后备,则可以删除Transition_time<来自WHERE子句的@birthday,并将其添加到ORDER BY子句中,正如您在注释中所建议的那样. 总结

以上是内存溢出为你收集整理的mysql – 当日期存储为unix时间戳时,如何计算美国东部时间3月9日19日出生的人数？全部内容，希望文章能够帮你解决mysql – 当日期存储为unix时间戳时,如何计算美国东部时间3月9日19日出生的人数？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。