CODEWITHSUNDEEP
mysqlperformance
Dan
Dan

Reputation: 16276

MySQL - best data type for simple time with hours and minutes

I need to represent the due time for tasks in my MySQL 5 database.
Then, I need to store times like these:
_ 18
_ 9:15
_ 12:00

Now I am in dilemma whether to use the time datatype or just a 4-digit integer as I just need to store hours and minutes.
In the second case, I would store:
_ 1800
_ 0915
_ 1200

What are the implication of both the approaches?
Which solution would you implement given that the most important requirement is high performance in a huge dataset (millions of rows)?

The application code is written in PHP.

EDIT: I was thinking of an important point. If I use integers, I need to do some string manipulation with PHP when I display the time (to insert the semicolon) that probably nullify the gain with using integers

Thanks,
Dan

Upvotes: 13

Views: 21362

Answers (2)

Dan Grossman
Dan Grossman

Reputation: 52372

MySQL's TIME data type only takes 3 bytes. Use it.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Upvotes: 6

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

Use time datatype. Main benefit will be reusing existing time function embedded in MySQL.

It will probably be more efficient in terms of space using a smallint (only 2 bytes) but you will loose the ability to use existing functions to operate on fields whose semantic is a time value. I can think of formatting functions, time difference, time zones as examples of scenarios where you could take immediate advantage of the time datatype.

Upvotes: 13

Related Questions