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

Question

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

20/10/2014 05:39 PM

20/10/2014 05:39 AM

I know MYSQL timestamp format 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 guess there is a problem with date_default_timezone_set() function, How to solve this and get 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?

Solution 1

MySQL already knows how to parse many different types of date strings natively, using the STR_TO_DATE() function in combination with format strings used by DATE_FORMAT().

So, I would not involve PHP in this process at all, and instead allow MySQL to parse the input itself.

The format string you need to use is %d/%m/%Y %h:%i %p, where the %p represents AM/PM.

You can use the entire expression right in your INSERT statement, passing the strings directly from PHP assuming you have validated their format already.

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

...will correctly insert the DATETIME value 2014-10-20 17:39:00 into your table.

If you really prefer to do it in PHP first, use DateTime::createFromFormat() (PHP 5.3+) using the format string '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"

To get a MySQL formatted date back out of it,

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

If you are in the deeply unfortunate situation of having a PHP version older than 5.3, you can achieve similar results with strptime() but you'll need to assemble its array output into MySQL's string format.

Solution 2

This should work for you:

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

?>

Output:

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

Solution 3

I think you can use the following code. It's easier to understand:

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

Solution 4

This is the better way if you are using php

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

View more solutions

Comments

[Rizier123]

Didn't know about that function, very cool! Also that it recognize AM and PM.

[Rizier123]

Why downvote? It works. Maybe it's not the best answer, but there is nothing wrong with it.

[Sammitch]

And then to re-jigger it into a MySQL date string: $d->format('Y-m-d h:i:s')

[Michael Berkowski]

I agree, that's kind of harsh

[edgarmtze]

Doing php solution I get Message: Object of class DateTime could not be converted to string How to solve it?

[Michael Berkowski]

@cMinor How exactly are you using it? You need to call $d->format('Y-m-d H:i:s) to get a different string back out in MySQL's format, but the $d->date property happens to be in a usable format too.

[Michael Berkowski]

Actually, $d->date is probably not useful. Use $d->format('Y-m-d H:i:s') Example codepad.viper-7.com/p5Rong

[edgarmtze]

I get error when usin $d->format('Y-m-d H:i:s'); : Fatal error: Call to a member function format() on a non-object

[Michael Berkowski]

@cMinor $d is the DateTime object created with $d = DateTime::createFromFormat('d/m/Y H:i A', '20/10/2014 05:39 PM'); You must create it first, then call format() on it.

[Michael Berkowski]

@cMinor You can also do it as a one-liner if you only need the value once and won't reuse the object, by chaining the methods: echo DateTime::createFromFormat('d/m/Y H:i A', '20/10/2014 05:39 PM')->format('Y-m-d H:i:s');

[akavel]

Are you sure you fully validated the input? What if someone entered 99 as hours? or 19 PM? Library function can be expected to automatically handle for you many tricky cases you didn't even imagine yet. That's why as a general rule it's much better to use library functions when possible, instead of ad hoc hand-rolled attempts.

[Altimus Prime]

Why had anyone marked this down? It's a far easier solution than anything proposed above and I believe it to be equally performant, and it works in php version back to I don't remember when.