2

In my django project, I have a model with a Postgres DateTimeRangeField holding the time lapse between the start and the end of a long task. Something like this:

timespan = DateTimeRangeField()

Is it possible to use order_by on the queryset using either the lower or upper attributes of the range field?

elynch
  • 2,150
  • 1
  • 18
  • 20
  • Can you elaborate your question? in my opinion, your question is ambiguous. Define sort relation using both attributes. Post some sample data. Why default behaviour don't match your requirements? *Range fields support the standard lookups: lt, gt, lte and gte. These are not particularly helpful - they compare the lower bounds first and then the upper bounds only if necessary. This is also the strategy used to **order by** a range field. It is better to use the specific range comparison operators.* – dani herrera Nov 23 '15 at 09:18
  • If i'm getting it right, [django actually uses `tstzrange`](https://docs.djangoproject.com/en/1.8/ref/contrib/postgres/fields/#datetimerangefield) for that column. [In PostgreSQL, you could use](http://www.postgresql.org/docs/current/static/functions-range.html) `ORDER BY lower(timespan)` or `upper(timespan)` for what you want, but I don't know if it's possible to express that in django. – pozs Nov 23 '15 at 09:23
  • Also, the `ORDER BY range_col` works the same as `ORDER BY lower(range_col), upper(range_col)` in general, if that helps. – pozs Nov 23 '15 at 09:47
  • @danihp yeah, I actually didn't read your edited comment before I started to test is out. – pozs Nov 23 '15 at 10:28
  • Maybe relevant: http://stackoverflow.com/questions/1652577/django-ordering-queryset-by-a-calculated-field – pozs Nov 23 '15 at 10:29
  • @pozs, yea. I post comment as answer because, In my opinion, it is the answer of the question. Regards. – dani herrera Nov 23 '15 at 10:49

1 Answers1

1

Using either the lower or upper attributes of the range field is the order by default behaviour. Quoting DateTimeRangeField django docs:

Range fields support the standard lookups: lt, gt, lte and gte. These are not particularly helpful - they compare the lower bounds first and then the upper bounds only if necessary. This is also the strategy used to order by a range field. It is better to use the specific range comparison operators

dani herrera
  • 48,760
  • 8
  • 117
  • 177
  • This seems to be the right thing. A simple order_by on the range field itself does order based on the lower, which is good enough for me. Thank you – elynch Nov 23 '15 at 11:43