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

--
.............................................
(_)                     Tobias Rischer
"==='              tobias@stripped
" "
...still.loving.gnu..........................
```