MySQL Query Help

[Drakkon]

Say I have a table with events structured such that:
-----------------------------------------------------------
Event
---------
start int - unix timestamp --the starting day/time of the event
end int - unit timestamp -- the end time of the event
repeating enum('none','day','week','month','year')
------------------------------------------------------------
and I want to select all the events that occur (or re-occur) within April

Is it possible in one query to get all the events that occur within those given timeframes (given by the repeating column)? or how would i go about separating the calls to optimize my number of gets? Or is there a better way to store the start/end days/times to achieve this? I don't want to insert more rows than necessary.

 

[Crusty]

I'm having a hard time understanding your question.

Can you post SHOW CREATE TABLE `table_name`; or a DESCRIBE `table_name`;

as well as some sample data, and what your output from that sample data SHOULD be?

edit: Wouldn't hurt to post what you've come up with so far as well.

 

[Drakkon]

Table structure:

CREATE TABLE `emp_calendar` (
`evt_id` int(11) NOT NULL auto_increment,
`start_ts` int(11) NOT NULL,
`end_ts` int(11) NOT NULL,
`repeat` enum('none','day','week','month','year') NOT NULL default 'none',
PRIMARY KEY (`evt_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `emp_calendar`
--

INSERT INTO `emp_calendar` (`evt_id`, `start_ts`, `end_ts`, `repeat`) VALUES
(1, 1207346400, 1207350000, 'day'),
(2, 1207429200, 1207432800, 'month');

---------------------------------------------------------------------------------------------------
So in row 1 there is a timestamps to start 4/4/08 at 3:00 pm and end at 4/4/08 at 4:00 pm that repeats daily
Row 2 is 4/5/08 at 2 pm and end at 4/5/08 at 3 pm that repeats once a month

A select query for "April" (month = 4) would grab

START,END
4/5/08 2:00 p, 4/5/08 3:00 p
4/4/08 3:00 p,4/4/08 4:00 p
4/5/08 3:00 p,4/5/08 4:00 p
4/6/08 3:00 p,4/6/08 4:00 p
4/7/08 3:00 p,4/7/08 4:00 p
4/8/08 3:00 p,4/8/08 4:00 p
...
(repeats every day since event repeats daily)
...
4/30/08 3:00 p, 4/30/08 4:00 p

--------------------------------------------
I've gotten a select that can draw in all the events that occur during month (pretty easy where start >= timestamp for april 1 and end <= timestamp for april 30). And then query the yearly for the yearly, monthly for all the monthly events, daily for daily...but seems repetitive and getting a overlap

 

[Crusty]

Well, from what I can tell there is no reason to have the repeat field if you are going to be storing a record for each time the event occurs.

What I would do is split the data into two tables. One that describes each event, so you would store your evt_id, date_started, time_started, repeat. Then a table which actually stores the information about each event as they occurred. So something like unique_id, evt_id, date_started, date_ended, time_started, time_ended

With that structure you can quickly query your event_description table to figure out what events should have been run on a given date, and then with a join you can actually pull out the event data from the second table for the events you want to look at.

 

[Drakkon]

but how would you pull events that are in the future that have not occurred yet? so to display on like a calendar or listing? I don't really need to look to the past except for events that occured (I do have another table that is populated by a cron job every evening to insert events as they occur but not before just in case they are canceled).
Notice from my example select query it pulls dates in the future not just ones that have already happened. If i inserted 3-4 daily events for each person that I'm dealing (which is a possibility) with the DB would fill way too fast (yes its a VERY small db allocation - only 5mb or so).

 

[Crusty]

In order to predict what events would occur on any given day, you would first query for a list of all yearly events that have a start date of the current month and day of month, followed by all monthly events that occur on the given day of the month, and so on. Obviously you'd have to take special care for events that occur on days of months where other months don't have those same days, days 29-31. This way would also allow people to create events that aren't scheduled to start until the future. So the end result is that you can predict what events will occur on what days, as well as being able to look up past events to see their exact running time.

I would throw all of that logic into a stored procedure so all you have to do is give it a date or date range, and it'll return the list of all events sorted by date.