[Tfug] Representing Periods of Time In MySQL?

[Claude Rubinson]

On Mon, Jan 14, 2008 at 03:15:10PM -0700, Christopher Robbins wrote:
> I've been doing some database work for awhile now,
> but I always wondered - is there any "good" or "best" way
> to represent periods of time in MySQL?
> 
> I've used a start time (TIME) and an end time (TIME) to do
> so, but I'm beginning to wonder if there's an easier and better
> way?

Obviously, it depends upon what you're trying to do.  Assuming that
you're talking about timing the length of some event, that's what I've
generally done.  If all you care about is the length of time, just
store it as a single integer representing the nearest
second/minute/etc.  Sometimes, it's useful to record the start time
and the length of time (rather than the end time).  If you're
concerned about portability, the rule of thumb is to store date and
time values as integers offset from an epoch.

Now, if you're talking about something more complicated like
fiscal-vs-calendar periods, I've typically used some form of internal
hierarchical mapping.  I can probably dig up some old db schema if
that's what you're looking for.

Claude