Archive for July 3rd, 2009
Continuing the theme on overlapping ranges:
From Stack Overflow:
I have an event table that specifies a date range with
I have another date range, specified in code, that defines the current week as
I’d like to query all events for the week.
A quick reminder: we have two options to write a condition for such a query.
A more simple one:
start_date < @week_end AND end_date > @week_start
is, uh, more simple, but less efficient in SQL Server and MySQL.
A more complex one:
(start_date > @week_start AND start_date < @week_end)
(@week_start BETWEEN start_date AND end_date)
is more index friendly and is more performant in SQL Server, Oracle and MySQL (with a slight schema change to enable SPATIAL index support)
Now, let's create a sample table in PostgreSQL:
Read the rest of this entry »