0

For some reasons I'm required to store date as string 2023-03-03 and I have a requirement to filter this data using date range.

Given starting date (say 2023-03-01) and end date (2023-03-04) I have to find all the rows between the given dates.

Currently I'm using this query

Sample.objects.filter(date__range=["2011-01-01", "2011-01-31"])

which is giving results.

I'm not able to understand how it is working. I'm looking to see if this approach I'm following is right of if there's better approach to query this data.

WAS
  • 5
  • 2
  • take a look. [here](https://stackoverflow.com/a/4668718/16250404) and also [This](https://stackoverflow.com/a/4668703/16250404) – Hemal Patel Mar 09 '23 at 05:32
  • Because of comparing strings, checkout this https://stackoverflow.com/questions/33836197/can-i-django-filter-a-charfield-by-range – Javohir Elmurodov Mar 09 '23 at 05:49

1 Answers1

1

This is the result of one of the advantages of the ISO 8601 date format (which you are correctly using):

When you sort the dates alphabetically, the dates will be correctly ordered.

The django / postgres __range query can also compare alphabetically, so this works.

So when you have to use a text field for dates, you can totally put them into it in ISO 8601 format, and compare / order them as you wish, as long as you create an index on the column (db_index=True). It won’t be as efficient as normal postgres dates, and also date-specific postgres features need converting, so will be slower.

Denis Cornehl
  • 4,104
  • 1
  • 20
  • 24