How can records be queried based on the timezone of a related record?

Using Django
1

Hello,

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

readme-image.png
readme-image.png2190×526 204 KB

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?

2

Your “gut reaction” is the same as mine, with the exception that I would have first gone with the __range operator and ensured that the end_of_today field was created as (in text description) “today date + 1 day @ 12:00 AM, converted from user’s time zone to UTC”

I’m not familiar with the arrow library, so I don’t know what it can do for you. But the key idea here is that you want to create a timezone-aware datetime object in the person’s timezone with a time component of 0 on the next day.