How to store fractions of a second in mysql

Question

Hey,

I would like to store time data accurate to the centisecond. The mySQL manual states that all stored values will be rounded.

I see two decent options. The time above the decimal place can be stored as TIME, and below the decimal can be stored as a TINYINT. The other option might be to shift all the values up so that HH:MM:SS:cc becomes DD:HH:MM:SS. Any other thoughts or criticisms?

Thanks for the ideas.

-Storm

Answer 1

I'd say you should go with two columns here, one with a DATETIME value and another with a number for the centiseconds.

"Shifting" the time values will very likely do more harm than good, let alone the problems in understanding why on earth someone would want to store hours in the "days" part of the time. Besides, you couldn't store a value for centiseconds larger than 59, then.

Answer 2

What about converting the time to a unix timestamp (number of milliseconds since the epoch) and then storing that (as a int64). Then you can handle the math on the in and out of the DB. It would give you the flexibility of TIME + TINYINT, but only take one column.