blocks|key|1121812|text|问题是您使用的是'M'和'D'，这是一种文本表示形式，MySQL需要2010-02-06+19:30:13格式的数字表示形式|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1121813|Try：date('Y-m-d+H:i:s')，它使用数字等效项。|1121814|编辑:将G切换到H，虽然它可能没有影响，但您可能希望使用24小时格式和前导0。|1121815|entityMap^0|8|3|C|3|Y|J|0|4|J|0|4|1|8|1|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|K|$]]

The problem is that you're using <code>'M'</code> and <code>'D'</code>, which are a textual representations, MySQL is expecting a numeric representation of the format <code>2010-02-06 19:30:13</code>
Try: <code>date('Y-m-d H:i:s')</code> which uses the numeric equivalents.
edit: switched <code>G</code> to <code>H</code>, though it may not have impact, you probably want to use 24-hour format with leading 0s.

blocks|key|1121890|text|来自php的date()+manual+page的评论|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1121891|<?php+$mysqltime+=+date+('Y-m-d+H:i:s',+$phptime);+?>|code-block|syntax|javascript|1121892|您的'Y‘是正确的--这是一个完整的年份，但是'M’是一个三个字符的月份，而'm‘是一个两位数的月份。与“D”而不是“d”的问题相同。“g”是一个1位或2位数字的小时，其中“H”在需要时总是有一个前导0。|1121893|entityMap|0|LINK|mutability|MUTABLE|url|http://www.php.net/manual/en/function.date.php#85692^0|6|6|6|I|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

From the comments of php's <a href="http://www.php.net/manual/en/function.date.php#85692" rel="noreferrer"><code>date()</code> manual page</a>:
<pre><code>&lt;?php $mysqltime = date ('Y-m-d H:i:s', $phptime); ?&gt;
</code></pre>
You had the 'Y' correct - that's a full year, but 'M' is a three character month, while 'm' is a two digit month. Same issue with 'D' instead of 'd'. 'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.

blocks|key|1336933|text|这里有一种替代的解决方案:如果您将PHP中的日期作为时间戳，那么绕过PHP处理它，让DB通过使用FROM_UNIXTIME函数来处理它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1336934|mysql>+insert+into+a_table+values(FROM_UNIXTIME(1231634282));
Query+OK,+1+row+affected+(0.00+sec)

mysql>+select+*+from+a_table;

%2B---------------------%2B
%7C+a_date++++++++++++++%7C
%2B---------------------%2B
%7C+2009-01-10+18:38:02+%7C
%2B---------------------%2B|code-block|syntax|javascript|1336935|entityMap^0|1C|D|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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 <code>FROM_UNIXTIME</code> function.

<pre><code>mysql&gt; insert into a_table values(FROM_UNIXTIME(1231634282));
Query OK, 1 row affected (0.00 sec)

mysql&gt; select * from a_table;

+---------------------+
| a_date |
+---------------------+
| 2009-01-10 18:38:02 |
+---------------------+
</code></pre>

blocks|key|1121995|text|我使用以下PHP代码创建一个变量，并将其插入到MySQL+DATETIME列中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1121996|$datetime+=+date_create()->format('Y-m-d+H:i:s');|code-block|syntax|javascript|1121997|这将保存服务器的当前日期和时间。|1121998|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.

<pre><code>$datetime = date_create()-&gt;format('Y-m-d H:i:s');
</code></pre>

This will hold the server's current Date and Time.

blocks|key|1337064|text|$date_old+=+'23-5-2016+23:15:23';+
//Date+for+database
$date_for_database+=+date+('Y-m-d+H:i:s'",+strtotime($date_old));

//Format+should+be+like+'Y-m-d+H:i:s'`enter+code+here`|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1337065|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>$date_old = '23-5-2016 23:15:23'; 
//Date for database
$date_for_database = date ('Y-m-d H:i:s'&quot;, strtotime($date_old));

//Format should be like 'Y-m-d H:i:s'`enter code here`
</code></pre>

blocks|key|1337137|text|我使用这个函数(PHP7)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1337138|function+getDateForDatabase(string+$date):+string+{
++++$timestamp+=+strtotime($date);
++++$date_formated+=+date('Y-m-d+H:i:s',+$timestamp);
++++return+$date_formated;
}|code-block|syntax|javascript|1337139|旧版本的PHP+(PHP+<+7)|1337140|function+getDateForDatabase($date)+{
++++$timestamp+=+strtotime($date);
++++$date_formated+=+date('Y-m-d+H:i:s',+$timestamp);
++++return+$date_formated;
}|1337141|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I use this function (PHP 7)

<pre><code>function getDateForDatabase(string $date): string {
 $timestamp = strtotime($date);
 $date_formated = date('Y-m-d H:i:s', $timestamp);
 return $date_formated;
}
</code></pre>

Older versions of PHP (PHP &lt; 7)

<pre><code>function getDateForDatabase($date) {
 $timestamp = strtotime($date);
 $date_formated = date('Y-m-d H:i:s', $timestamp);
 return $date_formated;
}
</code></pre>

blocks|key|1337050|text|将时间戳格式化为MySQL+DATETIME列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1337051|strftime('%25Y-%25m-%25d+%25H:%25M:%25S',$timestamp);|code-block|syntax|javascript|1337052|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Format time stamp to MySQL DATETIME column :

<pre><code>strftime('%Y-%m-%d %H:%M:%S',$timestamp);
</code></pre>

blocks|key|1122029|text|用PHP格式化MySQL+datetime|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1122030|$date+=+"'".date('Y-m-d+H:i:s',+strtotime(str_replace('-',+'/',+$_POST['date'])))."'";|code-block|syntax|javascript|1122031|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Format MySQL datetime with PHP

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

blocks|key|1337103|text|如果不使用时间戳，则不需要使用PHP中的date()方法。如果dateposted是datetime列，则可以像这样插入当前日期：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1337104|$db->query("INSERT+INTO+table+(dateposted)+VALUES+(now())");|code-block|syntax|javascript|1337105|entityMap^0|V|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

There is no need no use the date() method from PHP if you don't use a timestamp. If <code>dateposted</code> is a datetime column, you can insert the current date like this:

<pre><code>$db-&gt;query("INSERT INTO table (dateposted) VALUES (now())");
</code></pre>

blocks|key|1337229|text|在PHP7%2B中使用DateTime类：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1337230|function+getMysqlDatetimeFromDate(int+$day,+int+$month,+int+$year):+string
{
+$dt+=+new+DateTime();
+$dt->setDate($year,+$month,+$day);
+$dt->setTime(0,+0,+0,+0);+//+set+time+to+midnight

+return+$dt->format('Y-m-d+H:i:s');
}|code-block|syntax|javascript|1337231|entityMap^0|9|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Using <code>DateTime</code> class in PHP7+:
<pre><code>function getMysqlDatetimeFromDate(int $day, int $month, int $year): string
{
 $dt = new DateTime();
 $dt-&gt;setDate($year, $month, $day);
 $dt-&gt;setTime(0, 0, 0, 0); // set time to midnight

 return $dt-&gt;format('Y-m-d H:i:s');
}
</code></pre>

blocks|key|1337211|text|为了寻找一个简单的答案，这让我抓狂。最后，我制作了这个函数，它似乎捕获了所有输入，并给出了一个正确的SQL字符串，或者至少是有效的和可检查的。如果它是1999-12-31，它可能是错误的，但不会在MySQL中抛出严重的错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1337212|function+MakeSQLDate($date)+{
++++if+(is_null($date))+{
++++++++//use+1999-12-31+as+a+valid+date+or+as+an+alert
++++++++return+date('Y-m-d',+strtotime('1999-12-31'));
++++}

++++if+(($t+=+strtotime($date))+===+false)+{
++++++++//use+1999-12-31+as+a+valid+date+or+as+an+alert
++++++++return+date('Y-m-d',+strtotime('1999-12-31'));
++++}+else+{
++++++++return+date('Y-m-d+H:i:s',+strtotime($date));
++++}
}|code-block|syntax|javascript|1337213|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This has been driving me mad looking for a simple answer. Finally I made this function that seems to catch all input and give a good SQL string that is correct or at least valid and checkable. If it's 1999-12-31 it's probably wrong but won't throw a bad error in MySQL.

<pre><code>function MakeSQLDate($date) {
 if (is_null($date)) {
 //use 1999-12-31 as a valid date or as an alert
 return date('Y-m-d', strtotime('1999-12-31'));
 }

 if (($t = strtotime($date)) === false) {
 //use 1999-12-31 as a valid date or as an alert
 return date('Y-m-d', strtotime('1999-12-31'));
 } else {
 return date('Y-m-d H:i:s', strtotime($date));
 }
}
</code></pre>

blocks|key|1122308|text|对公认答案的一个小补充:如果数据库datetime存储为UTC+(我一直都这么做)，那么您应该使用gmdate('Y-m-d+H:i:s')而不是date("Y-m-d+H:i:s")。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1122309|或者，如果您更喜欢让MySQL处理所有事情，正如一些答案所建议的那样，我可以插入MySQL的UTC_TIMESTAMP，结果是一样的。|1122310|注意:我理解这个问题指的是当前时间。|1122311|entityMap^0|H|8|1D|L|21|J|0|1A|D|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|K|$]]

A small addendum to accepted answer: If database <code>datetime</code> is stored as UTC (what I always do), you should use <code>gmdate('Y-m-d H:i:s')</code> instead of <code>date(&quot;Y-m-d H:i:s&quot;)</code>.
Or, if you prefer to let MySQL handle everything, as some answers suggest, I would insert MySQL's <code>UTC_TIMESTAMP</code>, with the same result.
Note: I understood the question referring to current time.

What is the correct format to pass to the <code>date()</code> function in PHP if I want to insert the result into a MySQL <code>datetime</code> type column?
I've been trying <code>date('Y-M-D G:i:s')</code> but that just inserts &quot;0000-00-00 00:00:00&quot; everytime.

PHP date() format when inserting into datetime in MySQL

如果我想将结果插入到MySQL datetime类型的列中，那么在PHP中传递给date()函数的正确格式是什么？我一直在尝试date('Y-M-D G:i:s')，但每次都会插入"0000-00-00 00:00:00“。

问在MySQL中插入到datetime时的PHP date()格式
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中插入到datetime时的PHP date()格式EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中插入到datetime时的PHP date()格式
EN