在MySQL中插入datetime时的PHP date（）格式

妖狐艹你老母 2023-03-05 03:18 38阅读 0赞

本文翻译自：[PHP date() format when inserting into datetime in MySQL][PHP date_ format when inserting into datetime in MySQL]

*What is the correct format to pass to the `date()` function in PHP if I want to insert the result into a MySQL `datetime` type column?* **如果我想将结果插入MySQL `datetime`类型列，那么在PHP中传递给`date()`函数的正确格式是什么？**

*I've been trying `date("YMD G:i:s")` but that just inserts "0000-00-00 00:00:00" everytime.* **我一直在尝试`date("YMD G:i:s")`但每次只插入“0000-00-00 00:00:00”。**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/9ijw/在MySQL中插入datetime时的PHP-date-格式][https_stackoom.com_question_9ijw_MySQL_datetime_PHP-date-]

--------------------

### \#2楼 ###

*I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.* **我使用以下PHP代码创建一个插入MySQL DATETIME列的变量。**

$datetime = date_create()->format('Y-m-d H:i:s');

*This will hold the server's current Date and Time.* **这将保留服务器的当前日期和时间。**

--------------------

### \#3楼 ###

使用PHP格式化MySQL datetime

$date = "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['date'])))."'";

--------------------

### \#4楼 ###

*The problem is that you're using `'M'` and `'D'` , which are a textual representations, MySQL is expecting a numeric representation of the format `2010-02-06 19:30:13`* **问题是你使用`'M'`和`'D'` ，这是一个文本表示，MySQL期望格式的数字表示形式`2010-02-06 19:30:13`**

*Try: `date("Ymd H:i:s")` which uses the numeric equivalents.* **尝试： `date("Ymd H:i:s")`使用数字等价物。**

*edit: switched `G` to `H` , though it may not have impact, you probably want to use 24-hour format with leading 0s.* **编辑：将`G`切换为`H` ，虽然它可能没有影响，但您可能希望使用带有前导0的24小时格式。**

--------------------

### \#5楼 ###

*From the comments of php's [`date()` manual page][date_ manual page] :* **从php的[`date()`手册页][date_ manual page]的评论：**

<?php $mysqltime = date ("Y-m-d H:i:s", $phptime); ?>

*You had the 'Y' correct - that's a full year, but 'M' is a three character month, while 'm' is a two digit month.* **你的'Y'是正确的 - 这是一整年，但'M'是一个三个月的月份，而'm'是两个月的月份。** *Same issue with 'D' instead of 'd'.* **与'D'而不是'd'相同的问题。** *'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.* **'G'是1或2位小时，其中'H'在需要时始终具有前导0。**

--------------------

### \#6楼 ###

*Here's an alternative solution: if you have the date in PHP as a timestamp, bypass handling it with PHP and let the DB take care of transforming it by using the `FROM_UNIXTIME` function.* **这是一个替代解决方案：如果您将PHP中的日期作为时间戳，则绕过使用PHP处理它并让DB负责使用`FROM_UNIXTIME`函数对其进行转换。**

mysql> insert into a_table values(FROM_UNIXTIME(1231634282));
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from a_table;
    
    +---------------------+
    | a_date              |
    +---------------------+
    | 2009-01-10 18:38:02 |
    +---------------------+

[PHP date_ format when inserting into datetime in MySQL]: https://oldbug.net/q/9ijw/PHP-date-format-when-inserting-into-datetime-in-MySQL
[https_stackoom.com_question_9ijw_MySQL_datetime_PHP-date-]: https://stackoom.com/question/9ijw/%E5%9C%A8MySQL%E4%B8%AD%E6%8F%92%E5%85%A5datetime%E6%97%B6%E7%9A%84PHP-date-%E6%A0%BC%E5%BC%8F
[date_ manual page]: http://www.php.net/manual/en/function.date.php#85692