annotation in a raw query

Using Django Using the ORM
1

I am writing a query to find entries in a table where the value of one of the fields changes and report the timestamps when this happens.

I have this working using a raw query as it needs to use a LAG and a comparison which I don’t believe is possible without a WITH which isn’t available in django

in the model ‘update_time’ is a DateTime.

Here is the code snippet:

Q = models.MyTable.objects.raw("""
WITH s2 as (
SELECT
  id,
  site_id,
  update_time,
  LAG(site_id,1,0) OVER(ORDER BY update_time) AS old_site,
  LAG(update_time,1,"2000-01-01 00:00:00.000001+00:00") OVER(ORDER BY update_time) AS old_time
  FROM my_table WHERE my_id=12345
)
SELECT id,update_time,site_id,old_time FROM s2 WHERE NOT(site_id=old_site);
""")

The problem is that old_time appears in the model as string, not a date time. It’s also missing any TZ info. Experimentally If I return it “AS” one of my other model fields that is defined as a DateTime then I get what I want, but can I do this legally?

2

I can’t address your questions about your data field directly, but…

You don’t need to use the WITH statement for your query.

You should be able to rewrite your SQL as:

select * from (
  select id, site_id, update_time,
  LAG(site_id,1,0) over (order by update_time) as old_site,
  LAG(update_time, 1, "2000-01-01 00:00:00.000001+00:00") 
      OVER(ORDER BY update_time) AS old_time
  from my_table where my_id = 12345
) as s2 where not(site_id = old_site);

which might put you in a position where you can resolve the issues with using the ORM.

3

Hi Ken,
Your rewrite does work as you suggest, thanks. (I know django reasonably, but I’m very new to the SQL-world of windows, partitions and lags and leads in SQL let alone in the ORM, so translating working SQL to Django is currently quite slow wading through the wall of unfamiliar jargon).

I’ve been digging through the django source code, specifically django/db/models/query.py and examining RawQuerySet.iterator but I don’t think there’s a way to wedge in the required hook into converters in order to supply the required conversion to a DateTime

4

Found this actually caused by sqlite3. with psql a datetime is returned as expected