Django + PostgreSQL: query returning incorrect results

Question

In my application, I have created a view on the PostgreSQL database and mapped that view to a non-managed model in Django.

For some reason, when I query this model in Django, one of the tuples returns with an incorrect result.

This is my CREATE VIEW code:

  CREATE VIEW test_view_a AS
  SELECT ROW_NUMBER() OVER (ORDER BY account_id, person_id)::int AS id,
  AVG(EXTRACT(epoch FROM date_trunc('second', meeting_time)))::int AS avg_meeting_time,
  AVG(EXTRACT(epoch FROM date_trunc('second', office_time)))::int AS avg_office_time,
  AVG(EXTRACT(epoch FROM date_trunc('second', traffic_time)))::int AS avg_traffic_time,
  account_id,
  person_id
  FROM test_view_b
  GROUP BY account_id, person_id
  ORDER BY account_id, person_id;

This is the data returned when I query directly in the database:

 id | avg_meeting_time | avg_office_time | avg_traffic_time | account_id | person_id 
----+------------------+-----------------+------------------+------------+-----------
  1 |            64800 |               0 |             1514 |          1 |         8
  2 |            14400 |               0 |            29290 |          3 |         9
  3 |            14400 |               0 |                0 |          3 |        10
(3 rows)

When I query this view through Django, the second and third lines are returned correctly, but the first line is returned with avg_meeting_time as -1144800 and avg_traffic_time as 1202400:

>>> from test.models import *
>>> queryset = ViewA.objects.all()
>>> '%i %i' % (queryset[0].avg_meeting_time, queryset[0].avg_traffic_time)
'-1144800 1202400'

I have tried querying this view directly through the django.db.connection object, and even have tried executing the query that generates the view this way. The result is always the same, different from what is on the database.

I am using a spatial database with PostGIS, if that matters. I have tried switching the backend to psycopg2, but got the same result.

What's going on?

Can you post the model that is being used?

Burhan Khalid
– Burhan Khalid

2012-07-07 01:06:23 +00:00
Commented Jul 7, 2012 at 1:06 — Burhan Khalid
– Burhan Khalid, Commented Jul 7, 2012 at 1:06

b1_ · Accepted Answer · 2012-07-07 17:38:32Z

1

Try to debug you

>>> from test.models import *
>>> queryset = ViewA.objects.all()
>>> '%i %i' % (queryset[0].avg_meeting_time, queryset[0].avg_traffic_time)

with

from django.db import connection
connection.queries

And agree with Burhan Khalid - model will be usefull

answered Jul 7, 2012 at 17:38

b1_

2,1261 gold badge27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rsajdok · Accepted Answer · 2014-01-13 10:34:09Z

0

You should check yours settings.py Do You have correct locale?

TIME_ZONE = 'America/Chicago'

answered Jan 13, 2014 at 10:34

rsajdok

111 bronze badge

Collectives™ on Stack Overflow

Django + PostgreSQL: query returning incorrect results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related