Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).

I was originally going to store it as TIME but then I realised MySQL doesn't support milliseconds in this type.

I will be storing the actual time taken from the users stopwatch, not the time when they ran and finished (this will be calculated before being inserted).

What would be the best field type to use? I was thinking either double or float, but I'm not sure that would work?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

Personally, I'd use integer types for duration

Example: 340,000 milliseconds is 340000 in an unsigned int column.

To me, datetime, time, date etc are for explicit points in time. 340 seconds is meaningless in that context. Having a start date/time allows the duration to be added of course

share|improve this answer
3  
There is a standard SQL interval data type for just this purpose. But not all DBMS (and definitely not MySQL) support this. –  a_horse_with_no_name Jan 9 '12 at 17:24
    
Thanks for your answer, would I need to convert it to a standard format as currently it is in the format 00:00:00:00? Could I set the column to allow that type of format? –  Elliott Jan 10 '12 at 10:36
    
@Elliott: for display, you could. It is just a number otherwise –  gbn Jan 10 '12 at 10:38
    
Thanks I'll do that! –  Elliott Jan 10 '12 at 14:50
1  
Note that your format probably is (or should be for readability) 00:00:00.000 (note the dot instead of colon and 3 digits for milliseconds. –  Jannes Jan 11 '12 at 20:42
add comment

It depends on how long you want to measure. If you won't be recording any times longer than 2^32, or 4294967296 ms than a regular unsigned int is just fine. That corresponds to about 50 days of clock time, btw.

If you were storing unix time stamps or larger intervals then look at using bigint. This gives you 8 bytes of space, or the ability to record times as long at 1.84467440737096e+19 ms (which is roughly 500 Million years)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.