How to convert AM / PM date and time string to 24 hour timestamp mysql format

Question

I am trying to insert a date and time into the mysql datetime field from a string having the following format dd / mm / yyyy hh: mm: ss AM / PM :

20/10/2014 05:39 PM

20/10/2014 05:39 AM

I know the timestamp format of MYSQL is yyyy-mm-dd hh: mm: ss or 0000-00-00: 00: 00: 00

So if I do:

$s = substr("20/10/2014 05:39 PM", 0, 10);
$h = date("G:i", strtotime($s));
list($day, $month, $year, $hour, $minute) = split('[/ :]', "20/10/2014 05:39 PM"); 
echo $d1me = $year . '-' . $month. '-' .  $day . ' ' . $h;

I get 2014-10-20 19:00

So, I think that there is a problem with the date_default_timezone_set () function, how to solve this problem and get the expected result?

20/10/2014 05:39 PM     ->   2014-10-20 17:39:00

20/10/2014 05:39 AM     ->   2014-10-20 05:39:00

How to do it?

Answer 1

MySQL , , STR_TO_DATE() , DATE_FORMAT().

, PHP , MySQL .

, , %d/%m/%Y %h:%i %p, %p AM/PM.

INSERT, PHP, , .

INSERT INTO your_table (timestamp_column) VALUES (STR_TO_DATE('20/10/2014 05:39 PM', '%d/%m/%Y %h:%i %p'));

... DATETIME 2014-10-20 17:39:00 .

PHP, DateTime::createFromFormat() (PHP 5.3+), 'd/m/Y H:i A'

$d = DateTime::createFromFormat('d/m/Y H:i A', '20/10/2014 05:39 PM');
var_dump($d);

class DateTime#2 (3) {
  public $date =>
  string(26) "2014-10-20 17:39:00.000000"
  public $timezone_type =>
  int(3)
  public $timezone =>
  string(15) "America/Chicago"

MySQL,

echo $d->format('Y-m-d H:i:s');
// 2014-10-20 17:39:00

PHP 5.3, strptime(), 'll MySQL.

Answer 2

:

<?php

    $input = "20/10/2014 05:39 AM";  //20/10/2014 05:39 PM

    list($day, $month, $year, $hour, $minute, $dayType) = preg_split('/[\/\s:]+/', $input); 
    echo $d1me = $year . '-' . $month. '-' .  $day . ' ' . ($dayType == "PM"?$hour+12: $hour) . ":" . $minute . ":00";

?>

:

2014-10-20 05:39:00  //2014-10-20 17:39:00

Answer 3

I think you can use the following code. This is easier to understand:

echo date("Y-m-d H:i", strtotime("20/10/2014 05:39 PM"));

Answer 4

This is the best way if you use php

echo date('Y-m-d H:i', strtotime('20/10/2014 05:39 PM'));

Answer 5

echo date ("Ymd H: i: s", strtotime ("20-10-2014 05:39 PM"));

Use the code above - but keep in mind that you have a "-" instead of a "/" in the input date.