hundreths of a second

[sseeley]

There is a problem with storing hundreths of a second within MYSQL.  Does anyone have a idea of how I can store 1 hours, 27 minutes, 15 seconds, and 85 hundreths of a second?

 

I am trying to store athletics times.

 

Many thanks in advance.

 

Regards

 

Stuart

[fenway]

Nope -- but depending on what you need, you can always store the unix timestamp in microseconds as a BIGINT UNSIGNED.

[ignace]

Store it as a DECIMAL and calculate it like:

 

d = (h . 60 ^ 2) + (m . 60) + s + (n . 1 / 60)

 

function time2dec($hours, $minutes, $seconds, $hundreths) {
    return $hours * 3600 + $minutes * 60 + $seconds + $hundreths * 1 / 60; // 1/60 ~ 0.017
}

[awebtech]

  Quote

There is a problem with storing hundreths of a second within MYSQL.  Does anyone have a idea of how I can store 1 hours, 27 minutes, 15 seconds, and 85 hundreths of a second?

What about to store hundredths value in a separate INT column ?

[ignace]

  Quote

  Quote

There is a problem with storing hundreths of a second within MYSQL.  Does anyone have a idea of how I can store 1 hours, 27 minutes, 15 seconds, and 85 hundreths of a second?

What about to store hundredths value in a separate INT column ?

 

Then he loses the flexibility of for example sorting

[awebtech]

  Quote

Then he loses the flexibility of for example sorting

ORDER BY datetime, hundredths;

 

And no problem 

[ignace]

1:27:15,85

1:15:12,95

 

returns:

 

1:15:12,85

1:27:15,95

 

According to you that would be correct?

[awebtech]

  Quote

1:27:15,85

1:15:12,95

 

returns:

 

1:15:12,85

1:27:15,95

 

According to you that would be correct?

According to me that would be not. Please, pay attention that you broken rows and mixed one with another. It can not be.

 

Please, check:

 

CREATE TABLE `athletics_time` (
`main_time` TIME NOT NULL ,
`hundredths` INT NOT NULL 
);

INSERT INTO `athletics_time` (
`main_time` ,
`hundredths` 
)
VALUES (
'1:27:15', '85'
), (
'1:15:12', '95'
)
;

SELECT * 
FROM `athletics_time` 
ORDER BY main_time, hundredths
;

[ignace]

  Quote

Please, pay attention that you broken rows and mixed one with another.

 

Oeps my bad I replied a bit to hasty. I had thought about it before I suggested the time2dec() using an extra column but forgot that MySQL does not sort by column but by row.

[awebtech]

  Quote

Oeps my bad I replied a bit to hasty. I had thought about it before I suggested the time2dec() using an extra column but forgot that MySQL does not sort by column but by row.

Yeah, a bit too hasty. But sseeley has few ways out now. Not only few columns.