Varchar or decimal data type for minutes, seconds, milliseconds

Question

I have a MySQL database table with working time in different countries. I am at a fork and ask if I should convert the current data type for runtime (varchar) to a decimal number.

One aspect that attracts me to the varchar data type is that I do not need to convert the incoming results (parsed through a php script) in seconds, and then back when it is dynamically extracted. My processing of the script ensures that 8 characters last each time, unless the DNF (not finished) of the athlete, which is also the information I would like to keep. DNF is displayed as "DNF" in the results.

So, should the running time be saved as 17: 40.57 or 1060.57? What are the advantages and disadvantages for each? Is there a better data type than what I already assumed was the correct types?

Also, if you choose 1060.57 as your answer, then how will I logically store DNF or DNS?

Answer 1

I initially suggested TIME and DATETIME data types, but I did not know that MySQL does not store microseconds in a column of any temporary data type (IE: TIME, DATETIME, etc.) .

FLOAT is clearly poor - even MySQL claims that it should be used only when accuracy is not a problem.

VARCHAR / CHAR is not a great idea, because there are no means to ensure consistency of the format. You can mix mm: ss: ff and decimal format - both will be accepted, but when displayed clearly look odd.

DECIMAL would be the best choice for data consistency and validation, given the limitations of MySQL. But this means that user-defined functions can get information if you want different formatting that would be available if MySQL temporary functions supported sufficient accuracy.

Other free databases, such as PostgreSQL, SQL Server Express, or Oracle Express, are worth considering as an alternative for better data type support.

Answer 2

I would save it as a DECIMAL number of seconds, with additional columns for storing DNF or DNS. If you want, you can use single enumeration for DNF and DNS, since they are mutually exclusive (provided that DNS does not start). This allows SUM , arithmetic, etc. Storage as VARCHAR does not allow for any interesting processing or filtration.

EDIT: changed to DECIMAL , for accuracy.