Best way to store event times in (My)SQL database

Question

I'm trying to decide on the best way to store event times in a MySQL database. These should be as flexible as possible and be able to represent "single events" (starts at a certain time, does not necessarily need an end time), "all day" and "multi day" events, repeating events, repeating all day events, possibly "3rd Saturday of the month" type events etc.

Please suggest some tried and proven database schemes.

Answer 1

Same way the cron does it? Recording both start and end time that way.

Answer 2

Table: Events

• StartTime (dateTime)
• EndTime (dateTime) null for no end time
• RepeatUnit (int) null = noRepeat, 1 = hour, 2 = day, 3 = week, 4 = dayOfMonth, 5 = month, 6 = year
• NthDayOfMonth (int)
• RepeatMultiple (int) eg, set RepeatUnit to 3, and this to 2 for every fortnight
• Id - if required, StartTime might be suitable for you to uniquely identify an event.
• Name (string) - name given to the event, if required

This might help. It would require a decent amount of code to interpret when the repeats are. Parts of the time fields that are at lower resolutions than the repeat unit would have to be ignored. Doing the 3rd saturday of the month woudln't be easy either... the NthDayOfMonth info would be required just for doing this kind of functionality.

The database schema required for this is simple in comparison with the code required to work out where repeats fall.

Answer 3

You need two tables. One for storing the repeating events (table repeatevent) and one for storing the events (table event). Simple entries are only stored in the event table. Repeating entries are stored in the repeatevent table and all single entries for the repeating event are also stored in the event table. This means that everytime you enter a repeating entry, you have to enter all the single resulting entries. You can do this by using triggers, or as part of your business logic.

The advantage of this approach is, that querying events is simple. They are all in the event table. Without the storage of repeating events in the event table, you would have complex SQL or business logic that would make your system slow.

create table repeatevent (
id int not null auto_increment, 
type int, // 0: daily, 1:weekly, 2: monthly, ....
starttime datetime not null, // starttime of the first event of the repetition
endtime datetime, // endtime of the first event of the repetition
allday int, // 0: no, 1: yes
until datetime, // endtime of the last event of the repetition
description varchar(30)
)

create table event (
id int not null auto_increment,
repeatevent null references repeatevent, // filled if created as part of a repeating event
starttime datetime not null,
endtime datetime,
allday int,
description varchar(30)
)

Answer 4

i am working on a planner application which loosely follows the iCalendar standard (to record events), read up RFC 2445 and see whether you are interested and you may be interested in this schema published by apple inc. icalendar schema

my database schema

event (event_id, dtstart, dtend, summary, categories, class, priority, summary, transp, created, calendar_id*, status, organizer_id*, comment, last_modified, location, uid); // i havn't start working on the recurring/all-day event for now

calendar(calendar_id, name);

organizer(organizer_id, name); // should have other properties like common name etc.

another more readable documentation that you may be interested in is located here

hope this helps

Answer 5

Use datetime and mysql's built in NOW() function. Create the record when the process starts, update your column that tracks the end time when it the process ends.