Events List

Napster

I am stuck on how to go about this so I am looking to get some suggestions from fellow board members.

Basically I am trying to make a simple event list that shows the next two days of events that are upcoming. Some events happen every year on the same day (holidays, and birthdays) and some events can only happen once like a meeting.

So my table looks like this:

hl_events
id INT NOT NULL AUTO_INCREMENT
event VARCHAR(255)
event_d INT(2)
event_m INT(2)
event_y INT(4)
yearly INT(1)

now my main problem is trying to come up with an SQL query that will only select the next two days of events. So if there are no events tomorrow but there are 2 events in two days and another two events in a week, it would select those.

The other problem I am trying to think around is say it is currently the end of the year but the next two events are in the next year.

Some restrictions I have are that if an event happens yearly the column "yearly" will have the value 1 and there will be no value in event_y. However, if the event only happens once then yearly will be 0 and event_y will have the year it takes place. As well, I do not want to delete any records.

itaym02

Select all the events which dates are bigger than today or yearly is 1. Group it by month and day. Limit it all to 2.

Napster

I thought about that...

But does it still deal with if today is Dec 31st and the next event is on Jan 2nd. Since 1/2 is before 12/31, won't it get ignored?

itaym02

dates are bigger than today or yearly is 1

I believe this should catch every thing.
What I forgot to mention is you ORDER it by complete date. This will have "ignore" the yearly events, but you can give them, in the query, the current year. This will be problematic too. I see now.
Why not union of two queries, one for normal-one time events and one for recurring events?

Napster

I am not even sure of how to do union of queries. I need to go google this.

Weedpacket

Why not store the date in a single field with a date type?

Napster

I thought about this, but then what about the recurring events?

Weedpacket

You've still got a YEARLY flag. If that's set, ignore the year.

itaym02

The year is a very serious index.
Those two elements (recurring and none-recurring) are different, use some different logic, use different indexes. I would have separated them into two tables.

Napster

I am thinking of doing something similar, the only problem right now I have is that dates before Jan 1st, 1970 seem to not work with FROM_UNIXTIME and UNIXTIMESTAMP.

Is there a work around for this?

itaym02

Should be as you are not the first to have dates before 1970.

But if you use the day | month | year columns you shouldn't have problems with that.
You have to check the actions in your app to see if this makes sense though (for example, if you have many GROUP BY days or by months etc).

Another solution that jump to mind is to use negative unix_timestamp, I am sure a quick google will bring some good results (Honestly, never needed that, so can't help with it).