Menu
how can I set my mysql database field "time" data type to only be HH:MM in the database, in my script the user only enters HH:MM and the DB automatically adds the last :SS digits, the problem is when I pull that value to edit, it adds the last digits also, which is kind of annoying, I can get rid of it with PHP and truncating off the end, but I was hoping for a way to set it in the DB to remove those last 2 SS digits for good.
393
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 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.
You have to use the TIME data type to represent the time of the day however to store only the minutes and seconds you need to use the DATE_FORMAT to format your time in your desired format. On a side note, do note that TIME datatype takes 3 bytes of space.
I don't believe you can configure your database to store the time without the SS. MySQL's TIME DataType reference: http://dev.mysql.com/doc/refman/5.1/en/time.html
You'll have to set the formatting to HH:MM either in the mysql query or in php after you pull the data.
In PHP:
$date = date('H:i', strtotime($db_date_value));
http://us3.php.net/manual/en/function.date.php
In MySQL:
DATE_FORMAT(date_created, "%H:%i") as date_created
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
59
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
