0

I'm working on a calendar like feature to a project of mine.

I have a table like the following: 

ID | Title             |Where     |tri| Start      | End        | tro
______________________________________________________________________
"4"|"Planingfrenzy"    |"Street 8"|"0"|"1395835200"|"1395846000"|"1"
"5"|"Other meeting"    |"Road 8"  |"0"|"1395140400"|"1395158400"|"1"
"6"|"Third meeting"    |"Lane 8"  |"0"|"1395819000"|"1395824400"|"1"
"8"|"Weekend at cyprus"|"Cyprus"  |"0"|"1395928800"|"1396162800"|"1"

I have a problem selecting alla events that happens in one day. I tried the following two queries, but they only return those events thats start and end at the same day.

/*
   Start is a unixtimestamp for the beginning of the day
   End is a unixtimestamp for the end of the day
*/

//This Returns to many events since all events that ends before the end timestamp is a match etc. 
SELECT * FROM events WHERE (start > ? OR end<?) 

//This matches all events that start and end at the same day. But a multi day event like "Weekend at cyprus" isn't returned since it is out of range
SELECT * FROM events WHERE (start > ? AND end<?)

Is there some way in MySQL or PHP to match if start/end range "touches" in the queried timestamp range?

Philip G
  • 4,098
  • 2
  • 22
  • 41
  • What values are you using for `?`? – Gordon Linoff Mar 25 '14 at 16:17
  • You want all the events that are on one defined day (i.e. all events that are on 7th march, 2015) or all events that only last one day? – Andresch Serj Mar 25 '14 at 16:20
  • @GordonLinoff As mentioned in the comment ? = is the first/last timestamp for the day. – Philip G Mar 25 '14 at 21:11
  • @AndreschSerj i want all events thats on a certain date. eg: I have an event that begins 13.00 march 7th and ends 12.00 9th march. If I check for events on march 8th I still want that event to show up! – Philip G Mar 25 '14 at 21:13

3 Answers3

3

Assuming that the two values of ? are the earliest and latest values in the day, I think you want:

where start < (?end) and end > (?start)

That is, there is an overlap when the start of one is before the end of the other, and vice versa.

In this answer (?end) is intended to be the last timestamp of the day and (?start) is intended to be the first.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

In this case, the correct expression is to use BETWEEN:

SELECT * FROM events WHERE 1 AND date BETWEEN start AND end

MySQL Documentation

Joseph Collins
  • 441
  • 2
  • 9
0

I'm going to assume that you want to catch any event that has either a start time or an end time within your defined time range.

I didn't actually test on your dataset but this should get you headed in the right direction regardless:

SELECT * FROM events 
WHERE (Start >= :start AND Start < :end)
OR    (End >= :start AND End < :end)

And because you have included the PHP tag in your question, here is an easy way to get the "start" and "end" timestamps you need for a given date, in my example, I will use "today" as the target date.

$timezone = new DateTimeZone('UTC');
$date = new DateTime('today', $timezone);

$start = $date->getTimestamp();
$end   = $date->add(new DateInterval('P1D'))->getTimestamp();

I hope that helps.

Petar Zivkovic
  • 970
  • 10
  • 20
  • @Strawberry is it the SQL or timestamps that are bothering you? :) – Petar Zivkovic Mar 26 '14 at 16:18
  • @Strawberry, I think I get what you're saying, but Gordon's solution assumes that both the start and end time both fall within the desired range, while my solution assumes that *either* the start or end time fall within a given range. It also depends on how you calculate your start/end times, I just wanted to give an example that might get OP headed in the right direction. – Petar Zivkovic Mar 26 '14 at 21:16
  • No. Think about it. Draw a diagram if you like. – Strawberry Mar 26 '14 at 22:15