sql - 从 postgres View 中选择时，Django 返回错误结果

Question

我有一个在 postgres 中定义的 View ，在它使用的数据的单独模式中。它包含三列:

mydb=# \d "my_views"."results"
View "my_views.results"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 Date      | date                  | 
 Something | character varying(60) | 
 Result    | numeric               | 

当我从 psql 或 adminer 查询它时，我得到如下结果:

bb_adminpanel=# select * from "my_views"."results";
    Date    |          Something          |    Result    
------------+-----------------------------+--------------
 2015-09-14 | Foo                         |  -3.36000000
 2015-09-14 | Bar                         | -16.34000000
 2015-09-12 | Foo                         | -11.55000000
 2015-09-12 | Bar                         |  11.76000000
 2015-09-11 | Bar                         |   2.48000000

但是，通过 django 查询它，我得到了一个不同的集合:(c是数据库上的游标对象)

c.execute('SELECT * from "my_views"."results"')
c.fetchall()
[(datetime.date(2015, 9, 14), 'foo', Decimal('-3.36000000')),
 (datetime.date(2015, 9, 14), 'bar', Decimal('-16.34000000')),
 (datetime.date(2015, 9, 11), 'foo', Decimal('-11.55000000')),
 (datetime.date(2015, 9, 11), 'bar', Decimal('14.24000000'))]

根本不匹配——前两行是正确的，但最后两行真的很奇怪——它们有一个偏移的日期，最后一条记录的结果是最后两行的总和。

我不知道为什么会这样，欢迎提出任何建议。

---

这是 View 定义:

 SELECT a."Timestamp"::date AS "Date",
    a."Something",
    sum(a."x") AS "Result"
   FROM my_views.another_view a
  WHERE a.status::text = ANY (ARRAY['DONE'::character varying::text, 'CLOSED'::character varying::text])
  GROUP BY a."Timestamp"::date, a."Something"
  ORDER BY a."Timestamp"::date DESC;

“另一个 View ”看起来像这样:

          Column           |           Type           | Modifiers 
---------------------------+--------------------------+-----------
 Timestamp                 | timestamp with time zone |  
 Something                 | character varying(60)    | 
 x                         | numeric                  | 
 status                    | character varying(100)   | 

(省略部分栏目)

Answer 1

问题的简单解释是:时区。

详细说明:当连接到 PostgreSQL 控制台时，您没有声明任何时区设置，但 django 会在每次查询时声明。这样，某些记录的时间戳将根据使用的时区指向不同的日期，例如数据

+-------------------------+-----------+-------+--------+
|        timestamp        | something |   x   | status |
+-------------------------+-----------+-------+--------+
| 2015-09-11 12:00:00 UTC | foo       |  2.48 | DONE   |
| 2015-09-12 00:50:00 UTC | foo       | 11.76 | DONE   |
+-------------------------+-----------+-------+--------+

使用时区 UTC 执行的 View 查询将为您提供 2 行，但使用时区 GMT-2 执行的查询将仅提供一行。因为在 GMT-2 时区中，第二行的 timestamp 仍在 2015-09-11 日。

要解决此问题，您可以编辑 View ，使其始终根据指定时区对日期进行分组:

 SELECT (a."Timestamp" AT TIME ZONE 'UTC')::date AS "Date",
    a."Something",
    sum(a."x") AS "Result"
   FROM my_views.another_view a
  WHERE a.status::text = ANY (ARRAY['DONE'::character varying::text, 'CLOSED'::character varying::text])
  GROUP BY (a."Timestamp" AT TIME ZONE 'UTC'), a."Something"
  ORDER BY (a."Timestamp" AT TIME ZONE 'UTC') DESC;

这样天数将始终根据“UTC”时区计算。