如何将日期和时间为AM/PM的字符串转换为24小时mysql时间戳格式 [英] How to convert string with date and time AM/PM to 24 Hour mysql timestamp format

问题描述

我正在尝试从具有以下 dd/mm/yyyy hh:mm:ss AM/PM格式的字符串的日期和时间插入mysql datetime字段

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

我知道MYSQL时间戳格式为yyyy-mm-dd hh:mm:ss或 0000-00-00:00:00:00

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

所以，如果我这样做:

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

我得到2014-10-20 19:00

所以我想date_default_timezone_set()函数有问题，如何解决这个问题并获得预期的结果?

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

该怎么做?

推荐答案

MySQL已经知道如何使用

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().

因此，我完全不会在此过程中涉及PHP，而是允许MySQL自己解析输入.

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

您需要使用的格式字符串是%d/%m/%Y %h:%i %p，其中%p代表AM/PM.

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

您可以直接在INSERT语句中使用整个表达式，假设您已经验证了它们的格式，则直接从PHP传递字符串.

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

...将正确地将DATETIME值2014-10-20 17:39:00插入表中.

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

如果您真的更喜欢先用PHP进行操作，请使用 DateTime::createFromFormat() (PHP 5.3+)，使用格式字符串'd/m/Y H:i A'

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"

要从中获取MySQL格式化的日期，

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

如果您的PHP版本早于5.3，则非常不幸.您可以通过

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.

这篇关于如何将日期和时间为AM/PM的字符串转换为24小时mysql时间戳格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！