Dealing with Time in Php/Mysql

Question

Im very confused. For example I'm adding a record which time should be also filled up, then I filled up the the time textbox with format like this 6:30 pm.

So how could I store this data into mysql database? What data type should I use?Because there are time,datetime and timestamp datatypes. What PHP functions should I use.

What exactly I wanted to achieve is to get user inputted time with the format H:MM am/pm (e.g 6:30 pm), store it in a database the proper way, and display or retrieve this data with formatting exactly the way it was entered.

Could anyone please help me understand how to store and retrieve times in mysql using Php. Please give me examples.

I know there are so many resources on the web, but I want direct and clear answers for just a newbie like me in programming.

Answer 1

If you take the time as 6:30 PM as user input then you can use strtotime() to convert it to a timestamp, which you can then format for storage in the database.

Use the MySQL TIME data type which requires the time format in HH:MM:SS (24 hour).

Example:

$dbFormat = date('H:i:s', strtotime('6:30 PM'));

Produces:

18:30:00

This is ready to insert into the database. When you retrieve it from the database, you can have MySQL format it back to the original format using the DATE_FORMAT() function.

SELECT DATE_FORMAT(mytime, '%l:%i %p') FROM mytable

Produces:

6:30 PM

Answer 2

You could use timestamp to store your time in the MySQL database.

Time to timestamp:

<?php
    $time = strtotime($yourTime); //yourTime is the time from your input box.
?>

Timestamp to time:

<?php
    $time = date('g:i a', $yourTime); //yourTime is the time from the database.
?>

For more information see: http://nl1.php.net/manual/en/function.date.php and http://php.net/manual/en/function.strtotime.php

Edit: You can save a timestamp value as INT(11) in your MySQL database.