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..........................
