CODEWITHSUNDEEP
mysqlsqldateselect
fabrik
fabrik

Reputation: 14375

SQL date intervals query

I am managing an event database. Every event has a start and end timestamp (INT, unix timestamp).

Currently i'm able to do the following things with a single SQL query:

The problem is when an event spans several days i can't list it on a date between it's start and end timestamps.

For example:

Event starts on 2011/05/25 and ends on 2011/05/27 i can't list it on the page 2011/05/26.

My actual SQL query is

SELECT * FROM `event` 
WHERE (`start` BETWEEN ? AND ?) OR (`end` BETWEEN ? AND ?) 
ORDER BY start ASC

The two bound parameters (unix timestamps) are automatically calculated depending on what kind of parameter given (a whole month, or a specific day)

Is it possible to get these events (that spans several days) on a day between it's two endpoints extending my query above?

Please let me know if i can clarify my question.

Update

Example:

event start: 1309125660 (2011-06-27 00:01:00)
end end: 1314050340 (2011-08-22 23:59:59)

select start: 1312408860 (2011-08-04 00:01:00)
select end: 1312495199 (2011-08-04 23:59:59)

This event won't appear when i trying to list events occurring 2011/08/4

Upvotes: 2

Views: 7432

Answers (3)

arnep
arnep

Reputation: 6241

When you want to query all events "today" (or some other date), event those starting in the past or "today" and ending "today" or in the future you need some query like:

SELECT * FROM `event` WHERE 
   (`start` >= :start) AND (`end` <= :end) OR
   (`start` <= :end) AND (`end` >= :start)
ORDER BY start ASC

with ? being your actual date.

Test data:

123456789
   nn     <-- :start, :end
 xx       1 
      xx  2 
  xx      3 s
    xx    4 s
  xxxx    5 s
   xx     6 s

Test query:

select * from event where 
(start >= 4 and end <= 5) or
(start <= 5 and end >= 4)

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78571

If I get the question right, with [:start, :end] being your date range of interest, you're looking for:

select *
 from event
where -- event started earlier, ends later
      start <= :start and :start <= end
   or -- event starts during [:start, :end]
      :start <= start and start <= :end
   or -- event ends during [:start, :end]  
      :start <= end and end <= :end;

If you're looking for a particular :day, use :day as :start and :day + 1 day as :end.

Upvotes: 3

Hck
Hck

Reputation: 9167

I think you should modify query like this:

SELECT * FROM `event` WHERE ? BETWEEN `start` AND `end` ORDER BY start ASC

where param ? is the current date or current timestamp.

Upvotes: 0

Related Questions