php 将php日期转换为mysql格式

convert php date to mysql format

提问by bollo

I have a date field in php which is using this code:

我在 php 中有一个使用此代码的日期字段：

$date = mysql_real_escape_string($_POST['intake_date']);

How do I convert this to MySql format 0000-00-00 for inclusion in db. Is it along the lines of: date('Y-m-d' strtotime($date);. The reason I ask, is because I have tried variations of this and I cannot seem to get it to work. Either displays as 1970 or some other variation of that. Many thanks

如何将其转换为 MySql 格式 0000-00-00 以包含在 db 中。是这样的： date('Ymd' strtotime($date);非常感谢

回答by Shef

$date = mysql_real_escape_string($_POST['intake_date']);

1. If your MySQL column is DATEtype:

1. 如果您的 MySQL 列是DATE类型：

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

2. If your MySQL column is DATETIMEtype:

2. 如果您的 MySQL 列是DATETIME类型：

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

You haven't got to work strototime(), because it will not work with dash -separators, it will try to do a subtraction.

您不必工作strototime()，因为它不适用于破折号-分隔符，它会尝试进行减法运算。

---

---

Update, the way your date is formatted you can't use strtotime(), use this code instead:

更新，您无法使用日期格式strtotime()，请改用以下代码：

$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '-- ', $date);
echo $date;

Output:

输出：

2009-07-02 00:07:00

回答by John Humphreys - w00te

This site has two pretty simple solutions - just check the code, I provided the descriptions in case you wanted them - saves you some clicks.

该站点有两个非常简单的解决方案 - 只需检查代码，如果您需要它们，我会提供描述 - 为您节省一些点击次数。

http://www.richardlord.net/blog/dates-in-php-and-mysql

http://www.richardlord.net/blog/dates-in-php-and-mysql

1.One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. The methods would be used as follows -

1.一种常见的解决方案是将日期存储在 DATETIME 字段中，并使用 PHP 的 date() 和 strtotime() 函数在 PHP 时间戳和 MySQL DATETIME 之间进行转换。这些方法将被使用如下 -

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );

2.Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this -

2.我们的第二个选择是让 MySQL 来做这项工作。MySQL 有我们可以用来在我们访问数据库的地方转换数据的函数。UNIX_TIMESTAMP 将从 DATETIME 转换为 PHP 时间戳，而 FROM_UNIXTIME 将从 PHP 时间戳转换为 DATETIME。这些方法在 SQL 查询中使用。所以我们使用这样的查询插入和更新日期 -

$query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";

回答by Gus

You are looking for the the MySQL functions FROM_UNIXTIME()and UNIX_TIMESTAMP().

您正在寻找 MySQL 函数FROM_UNIXTIME()和UNIX_TIMESTAMP().

Use them in your SQL, e.g.:

在您的 SQL 中使用它们，例如：

mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1311343579 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1311343579);
+---------------------------+
| FROM_UNIXTIME(1311343579) |
+---------------------------+
| 2011-07-22 15:06:19       |
+---------------------------+
1 row in set (0.00 sec)

回答by Bailey Parker

If intake_date is some common date format you can use date()and strtotime()

如果intake_date 是一些常见的日期格式，您可以使用date()和strtotime()

$mysqlDate = date('Y-m-d H:i:s', strtotime($_POST['intake_date']));

However, this will only work if the date format is accepted by strtotime(). See it's doc pagefor supported formats.

但是，这仅在日期格式被 接受时才有效strtotime()。有关支持的格式，请参阅它的文档页面。

回答by Warren Sergent

Where you have a posted date in format dd/mm/yyyy, use the below:

如果您有 dd/mm/yyyy 格式的发布日期，请使用以下内容：

$date = explode('/', $_POST['posted_date']);
$new_date = $date[2].'-'.$date[1].'-'.$date[0];

If you have it in mm/dd/yyyy, just change the second line:

如果您在 mm/dd/yyyy 中有它，只需更改第二行：

$new_date = $date[2].'-'.$date[0].'-'.$date[1];

回答by Charlie

PHP 5.3 has functions to create and reformat at DateTime object from whatever format you specify:

PHP 5.3 具有从您指定的任何格式创建和重新格式化 DateTime 对象的函数：

$mysql_date = "2012-01-02";   // date in Y-m-d format as MySQL stores it
$date_obj = date_create_from_format('Y-m-d',$mysql_date);
$date = date_format($date_obj, 'm/d/Y');
echo $date;

Outputs:

输出：

01/02/2012

MySQL can also control the formatting by using the STR_TO_DATE()function when inserting/updating, and the DATE_FORMAT()when querying.

MySQL 还可以通过STR_TO_DATE()在插入/更新时和DATE_FORMAT()查询时使用该函数来控制格式。

$php_date = "01/02/2012";
$update_query = "UPDATE `appointments` SET `start_time` = STR_TO_DATE('" . $php_date . "', '%m/%d/%Y')";
$query = "SELECT DATE_FORMAT(`start_time`,'%m/%d/%Y') AS `start_time` FROM `appointments`";

回答by TMS

function my_date_parse($date)
{
        if (!preg_match('/^(\d+)\.(\d+)\.(\d+)$/', $date, $m))
                return false;

        $day = $m[1];
        $month = $m[2];
        $year = $m[3];

        if (!checkdate($month, $day, $year))
                return false;

        return "$year-$month-$day";
}

回答by Smar

There is several options.

有几种选择。

Either convert it to timestamp and use as instructed in other posts with strtotime()or use MySQL's date parsing option

将其转换为时间戳并按照其他帖子中的说明使用strtotime()或使用MySQL 的日期解析选项

回答by Ehtesham

If you know the format of date in $_POST[intake_date] you can use explode to get year , month and time and then concatenate to form a valid mySql date. for example if you are getting something like 12/15/1988 in date you can do like this

如果您知道 $_POST[intake_date] 中的日期格式，您可以使用爆炸来获取年、月和时间，然后连接以形成有效的 mySql 日期。例如，如果您收到类似 12/15/1988 的日期，您可以这样做

    $date = explode($_POST['intake_date'], '/');
    mysql_date = $date[2].'-'$date[1].'-'$date[0];

though if you have valid date date('y-m-d', strtotime($date)); should also work

但是如果你有有效的日期 date('ym-d', strtotime($date)); 也应该工作