时间戳

首页 > 解决方案 > 如何在 Postgres 中存储社交媒体 Unix 时间戳，保留用户的本地时间

postgresql - 如何在 Postgres 中存储社交媒体 Unix 时间戳，保留用户的本地时间

问题描述

几个月来我一直担心这一步，但我不可避免地要理解我们目前所处的时间混乱。我知道有很多可用的信息，但我在应用这个概念时仍然遇到问题我的问题的当地时间：

基本上，我们有一个 Postgres 数据库，我们在其中存储不同的社交媒体帖子（Foursquare、Flickr 等）以供分析。API 通常返回 Unix 时间戳，据我所知，它始终是 UTC。

问题是：当我们分析这些数据时，我们并不关心 UTC，我们需要知道用户在社交媒体上发帖时的当地时间。例如，假设有人在悉尼当地时间下午 4 点 (GMT+10) 拍摄了悉尼歌剧院的照片。我们如何将它存储在 Postgres -timestamp with timezone或timestamp without timezone？用户的本地时间是否可用 - 或者我们是否必须考虑地理坐标（如果可用）来计算用户的本地时间？

总结：

在这些情况下，存储元数据的最佳方法是什么？

[编辑] 我查询了用户在帖子中提到“o'clock”的位置，并将其与 API 返回的 post_publish_date（没有时区的时间戳）进行比较。令人惊讶的是，我所看到的表明所有这些时间戳都是当地时间，而不是 UTC：

"post_publish_date","post_body"
"2016-12-06 07:27:07","[...] at 8 o'clock a.m. [...]"
"2018-02-22 05:21:53","[...] main 6 o'clock road to [...]"
"2018-01-27 06:13:04","[...] get up early otherwise you miss [...] 6 o'clock [...]"
"2018-02-09 16:21:37","It's Friday [...] its gotta be 5 o'clock [...]"
"2018-02-02 15:44:21","It's Friday, [...] it's always 5 o'clock [...]"
"2015-11-21 02:37:53","[...] until 4 o'clock in the morning. [...]"
"2017-09-15 07:51:53","[...] 9 o'clock at night[...]"
"2017-12-18 19:52:40","[...]Date: ♨18.12.2017  [...] 20o'clock [...] Location: New York[...]"

这是一篇很好的文章，解释了这些差异，但据说Unix 时间戳可以在任何时区 - UTC 或本地时间，除非给出时区，否则永远不知道。现在的问题是：我可以将带有时间戳但不带时区的帖子存储在与指定时区的帖子相同的列（格式为timestamp with timezone）中吗？

这是另一篇文章，描述了如何在发推文时从用户各自的坐标中检索推文的实时时间。因此，至少 Twitter 返回的时间是 UTC 时间，而不是本地时间。

标签： postgresqlunix-timestamptimezone-offsetlocaltimesocial-media

解决方案

经过一番测试，我想我得到了正确的答案：

不同的社交媒体服务以不同的方式处理时间戳和时区。时间的三个方面是相关的：

  • (1)用户当地时间（帖子创建或发布时）
  • (2)绝对 UTC/GMT 时间（UTC 自 1970 年 1 月 1 日以来经过的秒数，与 GMT 相同），例如与全球时间比较相关
  • （3）查看者的当地时间（例如在线查看推文时，这可以防止看到将来发布的东西的悖论）

示例：(1)一条推文于柏林 9:37 (+2 CEST) 发布，(2) Twitter 将其存储为 7:37 UTC (+00)，以及(3)当这条推文在加利福尼亚被查看时 (- 7 PT），用户看到的时间是 12:37 AM（转换为观众的当地时间）。

虽然 (3) 与 Web-Apps 相关，但在数据分析中，人们通常对贡献用户的本地时间感兴趣，而不是 UTC 时间戳或查看者的本地时间。例如，对于 Flickr 和 Instagram，此时间可直接从 API 获得。对于 Twitter，必须根据有时可用的其他标准计算本地时间。

这对于在 Postgres 中存储数据意味着什么？

在 postgres 中，有两种选择：将时间保存为timestamp without timezone或timestamp with timezone. 但是，Postgres永远不会在 timestamp 中存储时区信息，这里的“timezone”仅指显示Postgres 时间戳时时间戳的格式（3）。因此，在存储数据进行分析时，不应处理时间戳。之所以将它们处理为 a，是timestamp without timezone因为贡献用户的时区最初是未知的（不考虑其他信息）。对于某些服务，例如 Twitter，分析人员的任务是在分析之前将此时间转换为用户的本地时间（例如，通过考虑 utc_offset、帖子的位置、语言或其他属性）。

推荐阅读