Django raw sql/postgres 时区混乱

Question

我花了最后一天的时间试图从我的数据库中获取时间序列的聚合。我尝试使用Django ORM，但很快就放弃了，转回SQL。我不认为有办法使用 PSQL generate_series，我认为他们更喜欢你在 python 中使用 itertools 或其他方法。

我有一个模型，很像这样：

class Vote(models.Model):
    value = models.IntegerField(default=0)
    timestamp = models.DateTimeField('date voted', auto_now_add=True)
    location = models.ForeignKey('location', on_delete=models.CASCADE)

我想做的是显示一段时间内的一系列指标 - 现在，当前用户当天每小时的聚合。用户设置了时区(默认为"美国/芝加哥"(。我一直在使用 postgres 查询，插入大量 As 时区转换，以努力解决查询的边界和返回值。昨晚我让它返回正确的结果，但今天早上，它又关闭了。我知道我正在做的事情一定是非常愚蠢的。我什至诉诸双播时间戳，因为 Postgres 在时区处理的奇怪方式(更正为 UTC 而不是 FROM(

同样，我想显示用户当前一天中每小时的聚合桶，直到/包括"现在"。

这是我当前的查询：

WITH hour_intervals AS (
    SELECT * FROM generate_series(date_trunc('day',(SELECT TIMESTAMP 'today' AT TIME ZONE 'UTC' AT TIME ZONE %s)), (LOCALTIMESTAMP AT TIME ZONE 'UTC' AT TIME ZONE %s), '1 hour') start_time
)
SELECT f.start_time,
COUNT(id) total,
COUNT(CASE WHEN value > 0 THEN 1 END) AS positive_votes,
COUNT(CASE WHEN value = 0 THEN 1 END) AS indifferent_votes,
COUNT(CASE WHEN value < 0 THEN 1 END) AS negative_votes,
SUM(CASE WHEN value > 0 THEN 2 WHEN value = 0 THEN 1 WHEN value < 0 THEN -4 END) AS score
FROM votes_vote m
RIGHT JOIN hour_intervals f 
        ON m.timestamp AT TIME ZONE %s >= f.start_time AND m.timestamp AT TIME ZONE %s < f.start_time + '1 hour'::interval
        AND m.location_id = %s
GROUP BY f.start_time
ORDER BY f.start_time

调试信息
Django 1.9.2和我的 settings.py 已经USE_TZ=True
Postgres 9.5.2和我的 django 登录角色有

ALTER ROLE yesno_django
  SET client_encoding = 'utf8';
ALTER ROLE yesno_django
  SET default_transaction_isolation = 'read committed';
ALTER ROLE yesno_django
  SET TimeZone = 'UTC';

---

更新再摆弄一下查询，现在是今天投票的工作查询......

WITH hour_intervals AS (
    SELECT * FROM generate_series((SELECT TIMESTAMP 'today' AT TIME ZONE 'UTC'), (LOCALTIMESTAMP AT TIME ZONE 'UTC' AT TIME ZONE %s), '1 hour') start_time
)
SELECT f.start_time,
COUNT(id) total,
COUNT(CASE WHEN value > 0 THEN 1 END) AS positive_votes,
COUNT(CASE WHEN value = 0 THEN 1 END) AS indifferent_votes,
COUNT(CASE WHEN value < 0 THEN 1 END) AS negative_votes,
SUM(CASE WHEN value > 0 THEN 2 WHEN value = 0 THEN 1 WHEN value < 0 THEN -4 END) AS score
FROM votes_vote m
RIGHT JOIN hour_intervals f 
        ON m.timestamp AT TIME ZONE %s >= f.start_time AND m.timestamp AT TIME ZONE %s < f.start_time + '1 hour'::interval
        AND m.location_id = %s
GROUP BY f.start_time
ORDER BY f.start_time

为什么我之前从晚上 7 点到晚上 10 点的查询工作得很好，但今天却失败了？我应该期望这个新查询也会失败吗？

有人可以解释我第一次(或每次(哪里出错吗？

Answer 1

首先，将related_name='votes'添加到位置的外键中，以便更好地控制，现在使用位置模型可以执行以下操作：

from django.db.models import Count, Case, Sum, When, IntegerField
from django.db.models.expressions import DateTime
queryset = location.objects.annotate(
    datetimes=DateTime('votes__timestamp', 'hour', tz),
    positive_votes=Count(Case(
        When(votes__value__gt=0, then=1),
        default=None,
        output_field=IntegerField())),
    indifferent_votes=Count(Case(
        When(votes__value=0, then=1),
        default=None,
        output_field=IntegerField())),
    negative_votes=Count(Case(
        When(votes__value__lt=0, then=1),
        default=None,
        output_field=IntegerField())),
    score=Sum(Case(
        When(votes__value__lt=0, then=-4),
        When(votes__value=0, then=1),
        When(votes__value__gt=0, then=2),
        output_field=IntegerField())),
    ).values_list('datetimes', 'positive_votes', 'indifferent_votes', 'negative_votes', 'score').distinct().order_by('datetimes')

这将为每个位置生成统计信息。您当然可以将其过滤到任何位置或时间范围。

Answer 2

如果您正在处理的日期时间字段允许空值，您可以使用以下内容 https://code.djangoproject.com/ticket/25937 解决方法：

Potato.objects.annotate(
    time=Coalesce(
        TruncMonth('removed', tzinfo=timezone.UTC()),
        Value(datetime.min.replace(tzinfo=timezone.UTC()),
    ).values('time').annotate(c=Count('pk'))

这将空时间替换为易于发现的哨兵。 如果你已经在使用 datetime.min ，你将不得不想出别的东西。

我在生产中使用它，但我发现TruncMonth()本身会给你本地时间，当你把Coalesce()放在它周围时，你只能有天真或UTC。