Bug #45905 calculate with date/time fields without datediff() / timediff()
Submitted: 2 Jul 2009 11:39 Modified: 15 Jul 2009 12:53
Reporter: Peter Lorenz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: date, DATEDIFF(), SQL Standard, time, timediff()

[2 Jul 2009 11:39] Peter Lorenz
Description:

Would be nice if it where possible to calculate difference between 
two datetypes or two timetypes without using datediff() and timediff() 

How to repeat:
For example on pervasive (also on old pervasive engines) it is possible
to do something like this:

select (endtime - begintime) from timejournal where personid = .... and projectid = ...

The database detects type of endtime and begintime (in this case datatype time)
and gives the result in hours:minutes:seconds for each record.

The same works with date-types,
there the result is given in days between dates.

It would be also nice if it where possible to do something like:
select sum(endtime - begintime) from timejournal where personid = .... and projectid = ...

In this case there would be only one result record with the time this person worked on that project. (in my example)

Also possible on pervasive is calculating with date and integer fields,
so it's possible to do an subtraction between a data-field and a timefield.

Example: 
select (documentdate+timestopay) from bils where rec = ...
12.01.2009 - 6 gives back 18.01.2009

This would increase the compatibility to other databases
and it will be more ease to migrate.

Well, our company have to correct all sourcecode cause of this incompatibility.

regards.

Suggested fix:

use datatypes from table definition and calculate.
[15 Jul 2009 12:53] Susanne Ebrecht
Many thanks for writing a reasonable feature request.