How to store fractions of a second in mysql

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.


13.10.2009 16:25:44

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.

13.10.2009 16:29:37

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.

13.10.2009 16:29:42
Unixtime is number of seconds since 1970-01-01. Hardly usable when needing sub-second precision.
Joey 13.10.2009 17:15:53
my bad, i thought it was milliseconds
Jim Deville 13.10.2009 18:57:31
Windows (NT) uses 100-ns intervals since 1601-01-01. That should be enough :-) But that's a different epoch. Then SQL Server uses 1753-??-?? as epoch. Weird things.
Joey 13.10.2009 22:28:15