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.

mysql requires yyyy-mm-dd hh:mm:ss format for its date time so doing it as 2013/06/18 won't work. As for how you do it you have 2 ways either using strtodate or manually exploding the strings and rearranging it both will work. — Dave, Jul 24 '13 at 13:55

Geoffrey · Accepted Answer · 2013-07-24T14:04:52.427

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";

edited Jul 24 '13 at 14:04

answered Jul 24 '13 at 13:59

Geoffrey

10,843
3
33
46

Thanks! I settled for a similar string method. – Sonu Jha Jul 24 '13 at 17:35

score 0 · Answer 2 · answered Jul 24 '13 at 13:57

0

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);

answered Jul 24 '13 at 13:57

DevZer0

13,433
7
27
51

While this is technically correct and would work, it should be noted that this will be converting the date/time to a UTC time stamp and back again, which means more overhead. If you do not need to process it in PHP, simple string manipulation will be faster then this method. – Geoffrey Jul 24 '13 at 14:01
i don't consider it to be that much overhead, its nano-seconds. this method deals with time and date in the context of time and date :-) – DevZer0 Jul 24 '13 at 14:07
That is not the point, overhead is overhead. Good programming practice is to write good fast code, especially since what you are doing here is 1) overkill, and 2) does not take into account the format of the date that is stored in $date (mm/dd or dd/mm). If you educate the next generation of programmers, they will write good code, if you don't, expect new code to be very poorly optimized. – Geoffrey Jul 24 '13 at 14:12

score 0 · Answer 3 · answered Jul 24 '13 at 14:16

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;
?>

score 0 · Answer 4 · answered Jul 24 '13 at 14:46

0

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');

answered Jul 24 '13 at 14:46

ital0

334
2
8

formatting date and time in php for sql insertion

4 Answers4