Handling Date and Time in PHP/MySQL

Date and time can be inserted into mysql table in specific format only. But php will support different types of date and time formats.

So, we should convert the date and time from php into specific format before inserting the value into mysql table.

We can use the date() and strtotime() functions of the php to do it.

MySQL table will store and datetime correctly only when it is formatted as Y-m-d H:i:s.

(i-e) 4 digit year-2 digit month – 2 digit day blankspace 24hours hour:minute:seconds.

Refer the below sample code used for inserting datetime into mysql table.

– Assume that Date and time values are stored in two different variables($dateinput,$timeinput) in php.
– First we need to concatenate both date and time to get single string.
– Then use strtotime() to convert this single string into time data which can be formatted using date() function to get required format.

include "config.php";//connected with database
$timeinput="10:30 pm";
$dt1=date( "Y-m-d H:i:s",strtotime($dateinput. " ".$timeinput));
$sql="insert into test (sttime) values ('$dt1')";
if (mysql_query($sql))
echo "successfully inserted";
echo mysql_error();

Similary strtotime() can be used to get previous and next days/weeks/months/years also.

For example below code will get the previous 1 week.

$fromDate=date(“Y-m-d”,strtotime($displayDate.”-1 week”));

Posted in MySQL, Php Tagged with: ,