Hi, there.
The problem
I am facing a strange behavior on a django query using the __date and __time lookups (also the __week_day). Basically, in our app we have events and blocks (time slots that cannot be allocated) in agendas, and the goal is to find future events that might conflict with a new block being created, to warn the user. The block have recurrence, for example ‘daily’.
I was using the following block of code to query the database, to find conflicts between a new daily block and existing events
class BlockService:
@staticmethod
def has_future_conflicts(
agenda: Agenda,
recurrence: Recurrence,
start_time: time,
end_time: time,
start_date: date,
end_date: date | None = None,
) -> bool:
"""Check if there are any conflicts with events in the agenda."""
... # more code here
if recurrence == 'daily':
events = BlockService._get_future_conflicting_events_daily(
agenda=agenda,
start_time=start_time,
end_time=end_time,
start_date=start_date,
end_date=end_date,
)
return events.exists()
@staticmethod
def _get_future_conflicting_events_daily(
agenda: Agenda,
start_time: time,
end_time: time,
start_date: date,
end_date: date | None = None,
) -> QuerySet[Event]:
# For daily events, we check future events that conflict with the block
# The time logic is the same as the _get_future_conflicting_events_none method
events = Event.objects.filter(
agenda=agenda,
start_date__date__gte=start_date, # Future events
start_date__time__lt=end_time, # Event start before block end
end_date__time__gt=start_time, # Event end after block start
)
if end_date is not None:
# If there is an end date, we limit to events until that date
events = events.filter(
end_date__date__lte=end_date,
)
return events
This code was failling a test (code and more info at the end). After some hours, I decided to rewrite the logic to query the exisiting events to this:
@staticmethod
def _get_future_conflicting_events_daily(
agenda: Agenda,
start_time: time,
end_time: time,
start_date: date,
end_date: date | None = None,
) -> QuerySet[Event]:
"""Check if there are any conflicts with events in the agenda."""
future_events = Event.objects.filter(
agenda=agenda,
start_date__date__gte=start_date,
)
ids: list[int] = []
for event in future_events:
if (
event.start_date.timetz() < end_time
and event.end_date.timetz() > start_time
):
ids.append(event.id)
return Event.objects.filter(id__in=ids)
If I am not mistaken, the logic remains the same. However, the second code for _get_future_conflicting_events_daily passes my test, while the first one does not.
Can some one help me understand this error?
About the tests
This is the test failing for ‘daily’ recurrence:
from datetime import datetime, timedelta
import pytest
from django.utils import timezone
from mixer.backend.django import mixer
from scheduling.models import Agenda, Event
from scheduling.services.blocks import BlockService
from services.models import Service
@pytest.fixture
def agenda() -> Agenda:
"""Fixture que cria uma agenda para os testes."""
return mixer.blend(Agenda) # type: ignore
@pytest.fixture
def service() -> Service:
"""Fixture que cria um serviço para os testes."""
return mixer.blend(Service, duration=timedelta(minutes=30)) # type: ignore
@pytest.fixture
def base_time():
"""Fixture que retorna um horário base para os testes."""
return timezone.now().replace(
hour=10,
minute=0,
second=0,
microsecond=0,
)
@pytest.mark.django_db
class TestBlockService:
@pytest.mark.parametrize('recurrence', ['none', 'daily', 'weekly'])
def test_has_future_conflicts_with_overlapping_event(
self, agenda, base_time: datetime, service: Service, recurrence
):
event_start = base_time - timedelta(minutes=30)
event_end = base_time + timedelta(minutes=30)
mixer.blend(
Event,
agenda=agenda,
start_date=event_start,
end_date=event_end,
service=service,
title='Overlapping Event',
)
start_date = base_time.date()
start_time = base_time.timetz()
end_time = (base_time + timedelta(hours=1)).timetz()
assert BlockService.has_future_conflicts(
agenda=agenda,
recurrence=recurrence,
start_time=start_time,
end_time=end_time,
start_date=start_date,
)
Adding some debug logs, we get something like this:
DEBUG root:test_blocks.py:76 Event: Overlapping Event - 18/04/2025 09:30, Agenda: Ms. Michelle Gallagher DVM's Agenda - Dr. Alfred Cross, Event Start: 2025-04-18 09:30:00+00:00, Event End: 2025-04-18 10:30:00+00:00, Recurrence: daily
DEBUG root:blocks.py:23 Checking conflicts for recurrence: daily on 2025-04-18 from 10:00:00+00:00 to 11:00:00+00:00
DEBUG root:blocks.py:106 Checking conflicts for events in the agenda: Ms. Michelle Gallagher DVM's Agenda - Dr. Alfred Cross, start_date: 2025-04-18, start_time: 10:00:00+00:00, end_time: 11:00:00+00:00, end_date: None
DEBUG root:blocks.py:53 Found 0 conflicting events: <QuerySet []>
Since the events start and end do overlap and are in the same timezone, I wasn’t expecting it to fail for the first block of code provided.
The tests were done using a PostgreSQL 17 database and runned with pytest and mixer.
Here is the Event model:
class Event(TimestampMixin):
CRIADO = 'criado'
AGENDADO = 'agendado'
CONFIRMADO = 'confirmado'
CANCELADO = 'cancelado'
CONCLUIDO = 'concluido'
STATUS_CHOICES = [
(CRIADO, _('Criado')),
(AGENDADO, _('Agendado')),
(CONFIRMADO, _('Confirmado')),
(CANCELADO, _('Cancelado')),
(CONCLUIDO, _('Concluído')),
]
StatusType = Literal[
'criado', 'agendado', 'confirmado', 'cancelado', 'concluido'
]
id: int
title = models.CharField(_('Título'), max_length=200)
description = models.TextField(_('Descrição'), blank=True)
start_date = models.DateTimeField(_('Data e Hora de Início'))
end_date = models.DateTimeField(_('Data e Hora de Término'))
agenda = models.ForeignKey(
Agenda,
on_delete=models.CASCADE,
related_name='events',
verbose_name=_('Agenda'),
)
status = models.CharField(
_('Status'),
max_length=20,
choices=STATUS_CHOICES,
default=CRIADO,
)
participants: 'models.ManyToManyField[Customer, models.Model]' = (
models.ManyToManyField(
Customer,
verbose_name=_('Participantes'),
related_name='events',
)
)
service = models.ForeignKey(
Service,
on_delete=models.PROTECT,
related_name='events',
verbose_name=_('Serviço'),
)
canceled_by = models.CharField(
_('Cancelado por'),
help_text=_('Nome do usuário ou cliente que cancelou o evento'),
max_length=255,
null=True,
blank=True,
)
conversation: 'models.ForeignKey[Conversation | None]' = models.ForeignKey( # type: ignore
'chatbot.Conversation',
on_delete=models.CASCADE,
related_name='events',
verbose_name=_('Conversa'),
null=True,
blank=True,
)
class Meta:
verbose_name = _('Evento')
verbose_name_plural = _('Eventos')
ordering = ['start_date']
def __str__(self):
return f'{self.title} - {self.start_date.strftime("%d/%m/%Y %H:%M")}'
The generated Django query:
SELECT
"scheduling_event"."id",
"scheduling_event"."created_at",
"scheduling_event"."updated_at",
"scheduling_event"."title",
"scheduling_event"."description",
"scheduling_event"."start_date",
"scheduling_event"."end_date",
"scheduling_event"."agenda_id",
"scheduling_event"."status",
"scheduling_event"."service_id",
"scheduling_event"."canceled_by",
"scheduling_event"."conversation_id"
FROM
"scheduling_event"
WHERE
(
"scheduling_event"."agenda_id" = 2
AND (
"scheduling_event"."end_date" AT TIME ZONE America / Sao_Paulo
) :: time > '10:00:00+00:00'
AND (
"scheduling_event"."start_date" AT TIME ZONE America / Sao_Paulo
) :: date >= '2025-04-18'
AND (
"scheduling_event"."start_date" AT TIME ZONE America / Sao_Paulo
) :: time < '11:00:00+00:00'
)
ORDER BY
"scheduling_event"."start_date" ASC