Menu
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
937
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
MySQL permits fractional seconds for TIME , DATETIME , and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name ( fsp ) , where type_name is TIME , DATETIME , or TIMESTAMP , and fsp is the fractional seconds precision.
The TIME data type consists of a time in hour, minutes, seconds, optional fractions of a second, and optional time zone.
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
