CODEWITHSUNDEEP
sqldjangopostgresqlsql-view
maniexx
maniexx

Reputation: 695

Django returns wrong results when selecting from a postgres view

I have a view defined in postgres, in a separate schema to the data it is using. It contains three columns:

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

When I query it from psql or adminer, I get results like theese:

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

However, querying it through django, I get a different set: (c is a cursor object on the database)

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'))]

Which doesn't match at all - the first two rows are correct, but the last two are really weird - they have a shifted date, and the Result of the last record is the sum of the last two.

I have no idea why that's happening, any suggestions welcome.

Here is the view definition:

 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;

and "another_view" looks like this:

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

(some columns ommited)

Upvotes: 1

Views: 333

Answers (1)

GwynBleidD
GwynBleidD

Reputation: 20539

Simple explanation of problem is: timezones.

Detailed: you're not declaring any timezone setting when connecting to PostgreSQL console, but django does it on each query. That way,the timestamp for some records will point to different day depending on used timezone, for example with data

+-------------------------+-----------+-------+--------+
|        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   |
+-------------------------+-----------+-------+--------+

query on your view executed with timezone UTC will give you 2 rows, but query executed with timezone GMT-2 will give you only one row. because in GMT-2 timezone timestamp from second row is still in day 2015-09-11.

To fix that, you can edit your view, so it will always group days according to specified timezone:

 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;

That way days will be always counted according to 'UTC' timezone.

Upvotes: 1

Related Questions