如何在Libreoffice calc中将格式化日期转换为Unix纪元

Question

我有一个带有日期或时间格式（DD.MM.YYYY HH:MM:SS）的列，包含以下数值：

03.12.2013 14:01:49 04.12.2013 10:19:27 04.12.2013 12:44:56 04.12.2013 14:20:12 04.12.2013 18:30:21

我需要将这些数值转换为Unix纪元时间（自1970年以来的秒数）。但它们好像被识别为字符串而不是日期。我尝试了不同的格式，但没有成功，即使没有时间的日期也无法识别。

- Pavel Niedoba

Answer 1

对于日期数据的操作应该是自动的，前提是单元格格式设定为用户定义的DD.MM.YYYY HH:MM:SS在“格式”>“单元格”>“数字”选项卡中。

如果您使用标准设置，LibreOffice Calc将12/30/1899作为其默认日期。因此，第一步是获取12/30/1899和1/1/1970之间的天数:

=(DATE(1970,1,1) - DATE(1899,12,30)) = 25569

一天有多少秒：

=(60 * 60 * 24) = 86400

如果例如在单元格A2中你有日期03.12.2013 14:01:49。我减去 Calc 的默认日期和我们刚刚计算的 Unix 纪元之间的差异，并将其乘以一天的秒数：

=(A2 - 25569) * 86400

结果是一个值为1363096909的时间戳，表示纪元时间（以秒为单位）。如果需要毫秒级别的时间戳，请将等式乘以1000。

如果您经常使用此功能，可以创建一个自定义函数来实现。转到“工具” > “宏” > “编辑宏”，并在出现的任何模块中键入以下内容：

REM  *****  BASIC  *****

Function EPOCH(date_cell)
EPOCH = (date_cell - 25569)*86400
End Function

关闭宏IDE，现在您可以像使用其他函数一样使用EPOCH()！

- SentientFlesh

---

这如何解释时代处于UTC时区？ - Walf

它不考虑UTC，因为问题没有要求使用UTC。如果要这样做，您将需要通过在计算中添加或减去小时数，或将Calc的默认时间设置为UTC来进行调整。 - jon.bray.eth

是的，我知道问题并没有特别要求，但标准的纪元时间戳总是使用协调世界时（UTC）。感谢额外提供的信息。 - Walf

@Walf，是的，除了用户数据（除非它们是UTC），其他所有内容都是UTC时间。如果您想编辑该函数以使用第二个参数，例如time_zone，请随意操作，否则我会在有机会时进行更新。如果您使用本地时间并且与UTC相差很远，则可能会出现偏差。我默认使用UTC时间。 - jon.bray.eth

Answer 2

这个公式对我起了作用，而上面的其他公式则没有：

= DATE( 1970, 1, 1 ) + ( A1 / 86400 )

- Bilbo