First and foremost, thank you for all the work that goes into the Django forum. I look forward to the weekly digests because they are are always illuminating.
I’m trying to understand how to use the Django ORM to query records based on a timezone stored on a related record.
All technical details in this post can be found in this github repo https://github.com/adamyala/timezone_lookup_example The repo contains all the code and a docker setup to recreate the data in this question.
Say we have the following 2 models in a todo list project.
TIMEZONES = ['US/Pacific', 'US/Mountain', 'US/Central', 'US/Eastern'] TIMEZONE_CHOICES = zip(TIMEZONES, TIMEZONES) class User(models.Model): timezone = models.CharField(max_length=255, choices=TIMEZONE_CHOICES) class Todo(models.Model): user = models.ForeignKey('app.User', on_delete=models.CASCADE) scheduled_datetime = models.DateTimeField()
Say we’d like to query all the Todo records that have reached their scheduled datetime, but are still within today in the user’s timezone.
An SQL query in Postgres to do that lookup might look like this. The most important column in the SELECT is
t.id. The extra columns in the SELECT statement are to help debug.
select t.id, u.timezone, t.scheduled_datetime, t.scheduled_datetime at time zone u.timezone as localized_scheduled_datetime, now() at time zone u.timezone as localized_now, date_trunc('day', now() at time zone u.timezone) + interval '23 hours 59 minutes' as end_of_today from app_todo as t join app_user as u on t.user_id = u.id where -- todo scheduled datetime is before the end of the user's today t.scheduled_datetime at time zone u.timezone < date_trunc('day', now() at time zone u.timezone) + interval '23 hours 59 minutes' and -- todo utc scheduled datetime is after utc now t.scheduled_datetime > now()::timestamp ;
The output of the query might look like this
I’ve read through different parts of the Django docs like https://docs.djangoproject.com/en/3.2/ref/models/database-functions/#date-functions and https://docs.djangoproject.com/en/3.2/ref/models/querysets/#annotate but I’m still not sure how to accomplish an ORM query like the SQL one above.
My gut reaction was to try something like
user_timezone = models.F('user__timezone') # this line throws an error because a string or tz object is expected end_of_today = arrow.today().to(user_timezone).floor('day').datetime Todo.objects.filter( scheduled_datetime__gte=arrow.utcnow().datetime, scheduled_datetime__lt=end_of_today, )
but that’s a bit off the mark. Apologies for bringing
arrow into this, its the datetime library I use.
Is this something that’s possible with the Django ORM or is this a scenario when we’d want to use raw SQL?