Dermy sqlheld wrote:
> The problem is, i want to calculate the average speed of the entries distance and time took,
> assumingly an easy thing... But the time is entered as above and i must recalculate it to an
> per-100-base-value, that is
> 1.25 --> 0.25 * 3/5 = 0.15 --> real time: 1.15 --> average speed = distance / real time
Are you sure about this formula? In my book, converting HH.MM to decimal format is done as follows: HH + MM/60
> If i have a column of type dec or float, for the entry time 1.25 i.e. (that means 1 hour and 25
> minutes), how can i get the fractional part of that number and how can i convert a float to a
> string?
If you run MySQL 4.1.1 or higher, none of that is necessary:
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
d DECIMAL(5,2),
f FLOAT
);
INSERT INTO foo (d, f) VALUES (1.25, 1.25), (1.30, 1.30);
SELECT
TIME_TO_SEC(STR_TO_DATE(FORMAT(d,2), "%H.%i"))/3600 AS dn,
TIME_TO_SEC(STR_TO_DATE(FORMAT(f,2), "%H.%i"))/3600 as fn
FROM foo;
Otherwise, this will work also:
SELECT
FLOOR(d) + (d - FLOOR(d))*5/3 AS dn,
FLOOR(f) + (f - FLOOR(f))*5/3 AS fn
FROM foo;
--
felix
Please use
BBCode to format your messages in this forum.