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?
解决方案
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.