在 MySQL 中插入日期时间时的 PHP date() 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2215354/
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
PHP date() format when inserting into datetime in MySQL
提问by Alex
What is the correct format to pass to the date()function in PHP if I want to insert the result into a MySQL datetimetype column?
date()如果我想将结果插入到 MySQLdatetime类型的列中，传递给PHP 函数的正确格式是什么？
I've been trying date("Y-M-D G:i:s")but that just inserts "0000-00-00 00:00:00" everytime.
我一直在尝试，date("Y-M-D G:i:s")但每次都只插入“0000-00-00 00:00:00”。
回答by Mark Elliot
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'and 'D'，这是一种文本表示，MySQL 期望格式的数字表示2010-02-06 19:30:13
Try: date("Y-m-d H:i:s")which uses the numeric equivalents.
尝试：date("Y-m-d H:i:s")使用数字等效项。
edit: switched Gto H, though it may not have impact, you probably want to use 24-hour format with leading 0s.
编辑：切换G到H，虽然它可能没有影响，但您可能希望使用前导 0 的 24 小时格式。
回答by Tim Lytle
From the comments of php's date()manual page:
来自 phpdate()手册页的评论：
<?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. Same issue with 'D' instead of 'd'. 'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.
'Y' 是正确的 - 那是一整年，但 'M' 是一个三个字符的月份，而 'm' 是一个两位数的月份。'D' 而不是 'd' 也有同样的问题。'G' 是 1 或 2 位数的小时，其中 'H' 在需要时总是有一个前导 0。
回答by JAL
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_UNIXTIMEfunction.
这是一个替代解决方案：如果您将 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 |
+---------------------+
回答by Steffan Clent Davies
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.
这将保存服务器的当前日期和时间。
回答by Ali Umair
$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`
回答by Ahmed Saber
Format time stamp to MySQL DATETIME column :
将时间戳格式化为 MySQL DATETIME 列：
strftime('%Y-%m-%d %H:%M:%S',$timestamp);
回答by dev4092
Format MySQL datetime with PHP
用 PHP 格式化 MySQL 日期时间
$date = "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['date'])))."'";
回答by SandroMarques
I use this function (PHP 7)
我使用这个函数（PHP 7）
function getDateForDatabase(string $date): string {
$timestamp = strtotime($date);
$date_formated = date('Y-m-d H:i:s', $timestamp);
return $date_formated;
}
Older versions of PHP (PHP < 7)
旧版本的 PHP (PHP < 7)
function getDateForDatabase($date) {
$timestamp = strtotime($date);
$date_formated = date('Y-m-d H:i:s', $timestamp);
return $date_formated;
}
回答by Adam
There is no need no use the date() method from PHP if you don't use a timestamp. If datepostedis a datetime column, you can insert the current date like this:
如果您不使用时间戳，则无需使用 PHP 中的 date() 方法。如果dateposted是日期时间列，您可以像这样插入当前日期：
$db->query("INSERT INTO table (dateposted) VALUES (now())");
回答by Brian Jones
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.
这让我发疯地寻找一个简单的答案。最后，我制作了这个函数，它似乎可以捕获所有输入并给出一个正确的或至少有效且可检查的良好 SQL 字符串。如果是 1999-12-31，它可能是错误的，但不会在 MySQL 中引发严重错误。
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));
}
}
