'ORM Query pulling objects outside date range due to Timezone change
I have a piece of legacy code that uses interpolated SQL with psycopg2 to perform a query. However I wanted to clean this up and make the code use the only the ORM. However I'm having an issue where my ORM query is pulling results outside of the given date range when the Timezone is set to non UTC.
For example, given the following model:
class Dinner(models.Model):
eaten_at = models.DateTimeField()
And giving the following arbitrary objects I have in my database:
Dinner(id=1, eaten_at=2022-02-23 16:58:11+00:00)
Dinner(id=2, eaten_at=2022-02-23 23:59:59+00:00)
Dinner(id=3, eaten_at=2022-02-24 00:00:00+00:00)
Dinner(id=4, eaten_at=2022-02-24 00:00:00+00:00)
I wanted to run a query for Dinners eaten on or after 2022-02-24
eaten_after_date = datetime.datetime.combine(datetime.datetime.strptime("2022-02-24", "%Y-%m-%d"),datetime.time(0, 0, 0))
# datetime.datetime(2022, 2, 24, 0, 0)
My Psycopg2 Code looked like this:
cursor.execute("SELECT * FROM dinner WHERE dinner.eaten_at >= %s with time zone", eaten_after_date)
As expected this results in two rows pulled with corresponding ID's 3 and 4.
I've re-written this using purely the ORM as:
Dinner.objects.filter(eaten_at__gte=eaten_after_date)
And in some instances- this works. However- when I have the following in settings:
TIME_ZONE = "Asia/Tokyo"
I return objects before the 24th (in this specific example- it returns ALL rows). For instance getting the first result sorted returns an entry from the 23rd:
record.objects.filter(meta_end_date__gte=eaten_after_date).order_by('eaten_at').first().eaten_after_date
>>> 2022-02-23 16:58:11+00:00
This is odd- my specified datetime is datetime.datetime(2022, 2, 24, 0, 0) and I'm pulling results from the 23rd! I'm positive this is some timezone issue when interpolating the query. However..I'm unsure how to fix it.
I've tried:
eaten_after_date = timezone.make_aware(eaten_after_date)
# and
eaten_after_date = eaten_after_date.astimezone()
However- I'm completely unable to replicate the query results from my previous Psycopg2 query into the pure ORM query.
I feel as if I'm missing something very simple and I'm completely unsure what.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
