2

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:

  • build up a calendar, and mark days when an event occurring
  • list occurring events on a given date (YYYY/MM/DD, YYYY/MM)

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

fabrik
  • 14,094
  • 8
  • 55
  • 71

3 Answers3

3

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.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
2

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)
arnep
  • 5,971
  • 3
  • 35
  • 51
  • This is select nothing for me. – fabrik May 26 '11 at 12:05
  • Can you provide you example timestamps to us? – arnep May 26 '11 at 12:09
  • @fabrik thank you for the example data. For me this is working: `SELECT * FROM event WHERE (start <= 1312408860) AND (end >= 1312495199) ORDER BY start ASC` with this data, I get one result in return. – arnep May 26 '11 at 12:26
  • @arnep sorry if i was equivocal. i'd like to use one query for all types of select. your query find the event spanning through a given day but it won't select anything for a given month. – fabrik May 26 '11 at 12:32
  • @fabrik I updated my query according to your needs of events starting in the past or now and ending now or in the future – arnep May 26 '11 at 13:00
0

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.

Hck
  • 9,087
  • 2
  • 30
  • 25