Django returns wrong results when selecting from a postgres view

Question

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)

What is "betbot_views"."daily_results" and why are you using this in your code instead of "my_views"."results"? Can you show your view definition (SQL code to re-create it)? — GwynBleidD
– GwynBleidD, Commented Sep 21, 2015 at 13:57
Also it will be helpful if you can show some example from source table(s) for your view. — GwynBleidD
– GwynBleidD, Commented Sep 21, 2015 at 14:00
@GwynBleidD the names were just a result of me forgetting to edit them out consistently, it's now fixed. As for the view definition, it's a rather complex view, depending on other views, and plenty of data. I would have hoped that shouldn't matter. One thing I can say, is that this data is not changing from call to call, as I've checked this with the exact results several times, over two days. — maniexx
– maniexx, Commented Sep 21, 2015 at 14:11
Have one possible solution, but to confirm that I must know what type of fields are in source data and how fields from view are computed. It's not something with cache, but rather with data types and environmental changes. — GwynBleidD
– GwynBleidD, Commented Sep 21, 2015 at 14:17

maniexx · Accepted Answer · 2015-09-21 14:57:39Z

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.

Collectives™ on Stack Overflow

Django returns wrong results when selecting from a postgres view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related