按用户时区检索聚合数据-解网

问：

我有一个 postgres 数据库，其所有数据都是 UTC 格式。如果我需要显示一天的任何数据或聚合，我只需将本地时间转换为 UTC 并在查询中使用它即可。

但是，现在我必须实现一个图表，显示每天聚合的月份数据。例如，根据用户每天添加的食物日志计算出的 6 个月内每天消耗的卡路里。

问题在于，如果用户位于比格林威治标准时间早 9 小时以上的任何时区，则在上午 9 点（作为早餐）添加的任何日志都将存储在前一天。并且它不会包含在结果中。

我最初的想法是从开始时间开始聚合超过 24 小时的间隔，但对于何时应用或删除 DST 来说，这不是一个好主意。一天有 23 或 25 小时会将所有后续数据丢弃 1 小时。

在客户端，我可以访问用户的时区。我脑海中的解决方案是将时区与请求一起发送，以便可以使用时间戳检查所有行。AT TIME ZONE 'clients-time-zone'

这似乎是一个很常见的问题，我找不到任何好的资源。这是一个好主意，还是有比这更好的方法？

就上下文而言，该应用程序在 Flutter 中，后端构建在使用 Postgres 的 Supabase 之上。中间没有 API 层，因为我使用数据库函数（存储过程）来获取所有必需的数据。

PostgreSQL 性能时间戳时区

@FrankHeikens 我认为我不需要存储每个日志的时区，因为无论您身在何处，UTC 都会保持一致。如果用户前往新区域，则只有其本地时间会更改。因此，一天中消耗的卡路里数量可能会发生变化，因为它们的当地时间不同。这对我来说是可以接受的，因为无法避免它。如果我的想法不正确，请告诉我。

0赞 Frank Heikens 11/16/2023

你对你的业务了解得更多，然后我就知道了;-）但我确实注意到，在时区之间旅行会导致不同的饮食模式：很长的一天，多吃一顿饭，或者很短的一天我不吃饭。而且如果不改变时区，23：00吃早餐有点奇怪，就像晚上03：00吃晚饭一样。这顿早餐，是太平洋标准时间23：00还是欧洲中部时间08：00？

0赞 muezz 11/16/2023

@FrankHeikens 你是对的。这有点奇怪，但我认为这更有利于一致性？你有什么建议？将用户的时区与每个日志一起保存会给聚合带来更多问题。在这种情况下，我应该使用什么标准？

0赞 George S 11/16/2023

如果要存储创建每个日志的时区，则将使用Laurenz Abe提供的相同聚合，只是它不是来自用户的设置，而是来自日志。我认为这在技术上是“正确的方式”，因为如果我从日本搬到伦敦，它不应该改变我每天的历史卡路里，但这可能不值得，这取决于你的用户资料（人们多久搬家一次？人们多久看一次他们的旧数据？Europe/Vienna

答：

1赞 Laurenz Albe 11/16/2023 #1

如果日期存储在的列中，该列的类型包含 UTC 数据，并且客户端位于时区中，则聚合如下：ttimestamp without time zoneEurope/Vienna

GROUP BY CAST (t AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Vienna' AS date)

第一个转换为绝对时间戳，第二个将其转换为当时维也纳挂钟显示的内容，并且类型转换会截断日期。AT TIME ZONEtAT TIME ZONE

只是出于好奇，如果我也开始保存时区，这样的查询会如何进行？我以为这样保存时区是没有用的。假设用户在加利福尼亚添加日志后从加利福尼亚转到维也纳，然后在维也纳添加日志。如果日志是在晚上 8 点在加利福尼亚添加的，我希望它会在凌晨 4 点出现在他们在维也纳的应用内日历中。因此，它将成为第二天消耗的总卡路里的一部分。

0赞 Laurenz Albe 11/17/2023

我不是在谈论保存时区。不保存时区。这是一个“绝对时间”。因为，加州的晚上 8 点与维也纳的凌晨 4 点相同。它们在磁盘上是相同的。timestamp with time zonetimestamp with time zone

0赞 muezz 11/17/2023

嗯。我以为有一种数据类型也可以存储偏移量。这个想法是你以UTC保存时间戳，但有偏移量。也许我错了？timestamptz

上一个：大查询中的日期差总和（以天为单位）

下一个：Flutter 时区包 tz.local 无法获取我所在地区的准确时间

按用户时区检索聚合数据

Retrieve aggregated data as per user time zone

评论

评论