CODEWITHSUNDEEP
phpmysqldate
Nick
Nick

Reputation: 906

pulling events from MySQL table by start and end date, retaining currently active events

I have a MySQL table that contains event data. The important columns are from_date and to_date. Both contain epoch timestamps to represent when the events starts, and ends.

I query the table to pull events that fall within a specified date range using:

from_date >= $from_date AND to_date <= $to_date

This is all good, but my problem now is that events that started before the from_date, but match the to_date rule is not being selected, ie the event started before the from_date but are still active... Same goes for events that match the from_date rule but pass the to_date. I need to see "active" events in the result also.

Any suggestions?

Upvotes: 2

Views: 210

Answers (3)

miah
miah

Reputation: 10433

If you we search for all events that start before the $to_date and end after the $from_date, it should pull what you are looking for.

from_date <= $to_date AND to_date >= $from_date

If your $from and $to are: 10:00 and 11:00. Then this query will give you:

  • 9:00 9:50 (no because 9:50 < 10)
  • 9:00 10:10 (yes)
  • 10:20 10:30 (yes)
  • 10:50 11:10 (yes)
  • 11:20 12:00 (no because 11:20 > 11:00)
  • 9:00 12:00 (yes because 9 is < 11 and 12 > 10)

Upvotes: 2

Halcyon
Halcyon

Reputation: 57709

Change your check to:

`from_data` BETWEEN $from AND $to OR `to_date` BETWEEN $from AND $to

In English: event starts or ends between $from and $to.

If your $from and $to are: 10:00 and 11:00. Then this query will give you:

9:00 9:50 (no)
9:00 12:00 (yes)
9:00 10:10 (yes)
10:20 10:30 (yes)
10:50 11:10 (yes)
11:20 12:00 (no)

Upvotes: 2

Useless Intern
Useless Intern

Reputation: 1302

I assume that you keep track of active events somehow to maybe you can try this:

  (from_date >= $from_date AND to_date <= $to_date) OR active = 1

This is an idea not copy/paste code

Upvotes: 0

Related Questions