List:General Discussion« Previous MessageNext Message »
From:Tobias Rischer Date:April 18 1999 8:33pm
Subject:Relative Time and arithmetics
View as plain text  
Dear List,

a question for advice and others' experience on the issue of relative
time (from hours to months) and of how to calculate with date and time:

There seem to be insular solutions: the TIME data type is recommended
also for relative time, and with DATE_SUB() and relatives, I can add to
absolute DATETIME using a special INTERVAL expression.

What I am missing is a more generic solution, like an INTERVAL column
type for all sizes of relative time (seconds to years), and arithmetic
functions for operations on DATE/TIME (and my INTERVAL), that work with
all combinations of absolute and relative time.  (Ideally an overloading
of +,-,*,/ where appropriate).

Since I am relatively certain, from reading/searching the manual, that
they aren't there (yet?), I'd be interested to hear of other peoples'
solutions when they have to deal with date/time arithmetic and relative
time.  Also, I was astonished that an INTERVAL column type wasn't even
mentioned as missing-though-wanted in the manual.  Does nobody feel that
gap? :-)

I have two current applications that I'd like to do well:

1) Daily working hours, consisting of:

   Beginning of work (abs. time, like "08:30");
   End of work       (abs. time, like "18:15");
   Breaks            (rel. time, like "00:45");

   Required: MySQL method of getting the net working time (9 hrs in the
   example).  Building on top of that also broader queries that produce
   the total time spent on a project, in a month, etc.  -- but that will
   be easy when the basic machinery works well.

2) Storage format for estimated project length and milestones (from
   hours to weeks/months), and ways to calculate with them, relative to
   calendar time.

Thanks for your attention,

       (_)                     Tobias Rischer
        "==='              tobias@stripped
         " "
Relative Time and arithmeticsTobias Rischer19 Apr
  • Relative Time and arithmeticsMichael Widenius19 Apr