php 如何将带有日期和时间 AM/PM 的字符串转换为 24 小时 mysql 时间戳格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27749613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert string with date and time AM/PM to 24 Hour mysql timestamp format
提问by cMinor
I am trying to insert date and time into mysql datetime field from a string having following dd/mm/yyyy hh:mm:ss AM/PMformat:
我正在尝试从具有以下dd/mm/yyyy hh:mm:ss AM/PM格式的字符串中将日期和时间插入到 mysql 日期时间字段中：
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
我知道 MYSQL 时间戳格式是 yyyy-mm-dd hh:mm:ss 或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
我得到 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?
所以我猜 date_default_timezone_set() 函数有问题，如何解决这个问题并得到预期的结果？
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?
怎么做？
回答by Michael Berkowski
MySQL already knows how to parse many different types of date strings natively, using the STR_TO_DATE()functionin combination with format strings used by DATE_FORMAT().
MySQL 已经知道如何在本地解析许多不同类型的日期字符串，将STR_TO_DATE()函数与DATE_FORMAT().
So, I would not involve PHP in this process at all, and instead allow MySQL to parse the input itself.
所以，我根本不会在这个过程中涉及 PHP，而是让 MySQL 自己解析输入。
The format string you need to use is %d/%m/%Y %h:%i %p, where the %prepresents AM/PM.
您需要使用的格式字符串是%d/%m/%Y %h:%i %p，其中%p代表AM/PM。
You can use the entire expression right in your INSERTstatement, passing the strings directly from PHP assuming you have validated their format already.
您可以在INSERT语句中直接使用整个表达式，假设您已经验证了它们的格式，直接从 PHP 传递字符串。
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 DATETIMEvalue 2014-10-20 17:39:00into your table.
...将正确地将DATETIME值2014-10-20 17:39:00插入到您的表中。
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'
如果您真的更喜欢先在 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"
To get a MySQL formatted date back out of it,
要从中获取 MySQL 格式的日期，
echo $d->format('Y-m-d H:i:s');
// 2014-10-20 17:39:00
If you are in the deeply unfortunatesituation 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.
如果您的 PHP 版本低于 5.3，那么您会非常不幸地遇到类似的结果，strptime()但您需要将其数组输出组合成 MySQL 的字符串格式。
回答by Rizier123
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
回答by Chirag Savaliya
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"));
回答by Jhon Martin
This is the better way if you are using php
如果您使用的是 php，这是更好的方法
echo date('Y-m-d H:i', strtotime('20/10/2014 05:39 PM'));
回答by Akash Patel
echo date("Y-m-d H:i:s", strtotime("20-10-2014 05:39 PM"));
echo date("Ymd H:i:s", strtotime("20-10-2014 05:39 PM"));
Use above code - but keep in mind you have "-" in place of "/" in input date.
使用上面的代码 - 但请记住，输入日期中有“-”代替“/”。
