Page 1 of 1

DataType ftTime limited to 24 hours

Posted: 05.03.2007, 10:46
by fred_nd
I have been playing around with generating time statistics from data in a MySQL database.

The problems I'm having is that I will sum up lots of small time values into one larger time value, and the result I get from the database can be something like "136:00:00". This is then converted to a TDateTime and then only the fraction is saved and what I can get out from the query will be something like "16:00:00".

At first I changed the AnsiSQLDateToDateTime in ZSysUtils to be able to create TDateTimes from large values, and to be able to handle negative time values as well. But then I noticed that all the functions where using Frac() to only keep the 24 hour part of the time values.

After removing most Frac() functions I then ended up with some Integer/TTimeStamp problems that I do not want to go into ;).

I think that I need to start over from the beginning.

Has there been any thoughts of supporting negative time values or time values larger than 24 hours?
Would there any problems with supporting these formats?

And last, maybe there is a practical way of doing this already?

/Fredrik

Posted: 05.03.2007, 14:33
by btrewern
What you need is an 'Interval' data type. I'm not sure MySQL supports it (BTW PostgreSQL does). If MySQL doesn't have such a thing you might be better off just using a large floating point data type. I think that is what TDateTime is anyway so it might be a good fit.

Regards,

Ben

Posted: 07.03.2007, 11:19
by andy
A TTime is only from 00:00:00 to 23:59:59.
If I have to sum times I use a TDateTime and format the output only as hours. So the internal TDateTime Value of 1.5 will result in 36:00 hours.