Hi!
>>>>> "Tobias" == Tobias Rischer <tobias@stripped> writes:
Tobias> Dear List,
Tobias> a question for advice and others' experience on the issue of relative
Tobias> time (from hours to months) and of how to calculate with date and time:
Tobias> There seem to be insular solutions: the TIME data type is recommended
Tobias> also for relative time, and with DATE_SUB() and relatives, I can add to
Tobias> absolute DATETIME using a special INTERVAL expression.
Tobias> What I am missing is a more generic solution, like an INTERVAL column
Tobias> type for all sizes of relative time (seconds to years), and arithmetic
Tobias> functions for operations on DATE/TIME (and my INTERVAL), that work with
Tobias> all combinations of absolute and relative time. (Ideally an overloading
Tobias> of +,-,*,/ where appropriate).
Tobias> Since I am relatively certain, from reading/searching the manual, that
Tobias> they aren't there (yet?), I'd be interested to hear of other peoples'
Tobias> solutions when they have to deal with date/time arithmetic and relative
Tobias> time. Also, I was astonished that an INTERVAL column type wasn't even
Tobias> mentioned as missing-though-wanted in the manual. Does nobody feel that
Tobias> gap? :-)
I have now added this to our TODO.
The problem is that the current .frm file doesn't allows us to easily
add new types; In MySQL 4.0 we plan to change the .frm file to a text
file and after this it will be easy to add new types.
(This will be the biggest difference between MYSQL 3.x and MySQL 4.x)
Tobias> I have two current applications that I'd like to do well:
Tobias> 1) Daily working hours, consisting of:
Tobias> Beginning of work (abs. time, like "08:30");
Tobias> End of work (abs. time, like "18:15");
Tobias> Breaks (rel. time, like "00:45");
Tobias> Required: MySQL method of getting the net working time (9 hrs in the
Tobias> example). Building on top of that also broader queries that produce
Tobias> the total time spent on a project, in a month, etc. -- but that will
Tobias> be easy when the basic machinery works well.
Tobias> 2) Storage format for estimated project length and milestones (from
Tobias> hours to weeks/months), and ways to calculate with them, relative to
Tobias> calendar time.
Tobias> Thanks for your attention,
Tobias> Tobias
For the moment the only 'nice' solution is to store the above as a
string. This string can then be used with DATE_SUB() exactly like a
true interval type.
Regards,
Monty
