formatting date and time in php for sql insertion

Question

I have a date, and a time, which I parse from a csv file, $date being a string, and it holds the value '18-06-2013', and the $time string var holds the value '06.00'. I need it formatted by php so that I can insert it into my mysql database in the format 2013/06/18 and 06:00:00. How do I do it? Right now, it inserts some zero junk values like 0000-00-00 for date and 00:00:06 for time.

Answer 1

You could split it on the '-' token and then join it back in the reverse order (assuming your input format is DD-MM-YYYY, which it seems to be)

$date = implode('-', array_reverse(explode('-', $date, 3));
$time = str_replace('.', ':', $time) . ':00';
$datetime = "$date $time";

Answer 2

You can pass your custom time format to strtotime and then format the time using date

$time = strtotime("$date $time");
echo date("Y-m-d h:i:s", $time);

Answer 3

Mysql requires DateTime fields in the format yyyy-mm-dd hh:mm:ss to be stored correctly.

Here is a test script for you to play with.

<?php
    $date = '2013/06/09';
    $time = '06.00';

    list($y, $m, $d) = explode('/', $date);
    list($hr, $mn)   = explode( '.', $time);

    $db_datetime = sprintf( '%d-%02d-%02d %02d:%02d:%02d', $y, $m, $d, $hr, $mn, 0 );

    echo $db_datetime;
?>

Answer 4

You can use DateTime class. It's native in PHP since PHP 5.2.0.

Example:

$myDate = '2013/06/18 06:00:00';
$myDateTime = new DateTime($myDate);

echo $myDateTime->format('Y-m-d H:i:s');