From: Michael Widenius
Date: April 19 1999 12:34am
Subject: Relative Time and arithmetics
List-Archive: http://lists.mysql.com/mysql/2043
Message-Id: <14106.30813.175014.246352@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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