如何按天统计数据并仍然依赖时区？ [英] How Do I aggregate Data By Day and Still Respect Timezone?

问题描述

我们正在使用汇总表，以UTC时间的方式在小时内汇总用户的信息。我们遇到的问题是这张桌子变得太大了，我们的系统下降了很多。我们已经完成了对PostgreSQL推荐的所有调优技术，我们仍然经历缓慢。

We are currently using a summary table that aggregates information for our users on an hourly basis in UTC time. The problem we are having is that this table is becoming too large and slowing our system down immensely. We have done all the tuning techniques recommended for PostgreSQL and we are still experiencing slowness.

我们的想法是以日期而不是小时开始聚合，但问题是我们允许我们的客户更改时区，重新计算当天的数据。

Our idea was to start aggregating by day rather than by hour, but the problem is that we allow our customers to change the timezone, which recalculates the data for that day.

有没有人知道一种方式来存储每日摘要，但是在切换时区时仍然尊重数字和总计？

Does anyone know of a way to store the daily summary but still respect the numbers and totals when they switch timezones?

推荐答案

使用timeoffset列和作为该特定摘要行的日期的day字段（日期）汇总表中的数据。索引（timeoffset，day，其他相关字段），如果可能，聚集（大概PostgresSQL有聚簇索引？），并且都应该很好。

Summarise the data in tables with a timeoffset column, and a "day" field (a date) that is the day for that particular summary line. Index on (timeoffset, day, other relevant fields), clustered if possible (presumably PostgresSQL has clustered indexes?) and all should be well.

这篇关于如何按天统计数据并仍然依赖时区？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！