Re: Modelling Event Times and Dates

Perhaps use 5 fields, like Unix cron uses for scheduling (gives the start time), together with a duration (in seconds?). For any field that is not required (represented by '*' wild card for cron), one could NULL the field in the database record. The intersection (logical AND) of non-NULL fields would specify either a particular time, or a recurring time. See any Unix machine "man"(ual) pages for the "cron" commands and/or files (crontab).

BTW, I remember seeing in some IBM DB2 documentation that the database/system timestamp could be specified up to 24:00 for any particular day. I had tried to use 2 timestamps to represent durations of activities during a particular day, and ran into problems with a work shift that started in the evening and went to midnight. That version of DB2 (1.3 for OS/2) did not accept 24:00, even though the documentation said it should. Since then, I've noticed that other databases don't accept 24:00 either. Is there a standard way to represent midnight (or actually a fraction of a second before midnight?)?

Ideally, one would like to be able to tell the database to use a time such as 24:00 on a particular date. I suppose this is really 23:59.9999... (in the limit it approaches 24:00). I found actually using times such as 23:59, or 00:00 (with the next date) to be awkward, and required extra logic to compensate/adjust. How have other people dealt with this?

FWIW, on one project we actually "extended the day" to compute shift work and overtime (with rules that applied to "same day"), by using hours >24:00 (e.g. 27:00 for 3am, etc.), obviously not using database time values. That was kind of bizarre. Worked for us, though.

Bill Buchan wrote:
> I'd be grateful for any advice which anyone could supply on modelling event
> times and dates within a RDBMS. I need to store irregular patterns such as
>
> 1/
> 18th - 29th May Mon. - Thu. 0930-1030, 1140-1200, 1530-1600 (except 20th and
> 21st), Fridays 1500-1530
> Closed Saturdays and Sundays
>
> 2/
> Every second Thursday at 1630
>
> 3/
> The first Tuesday in the month at 1900
>
> 4/
> The 1st of the month unless it's a weekend, in which case it's the following
> Monday.
>
> 5/
> Weekdays 1630 - 1800 29th August - 14th September
> Weekends 1700 - 1830
> Weekdays 1700 - 1830 15th September - 12th November + Thursdays at 1300-1430
>
> as well as straightforward times like 28th June at 1200.
>
> ie. There are many bizarre and complex combinations of dates and times.
> Has anyone got experience of tackling similar storage requirements: what is
> an effective and efficient way of storing this information?
>
> Any advice on this would be very gratefully received.
 

-- 

Juhan Leemet
Logicognosis, Inc.