如何将 PHP 中的 mysql 日期时间转换为 m/d/y 格式？

How to convert a mysql datetime in PHP to m/d/y format?

提问by JasonDavis

I am trying to convert a mysql DATETIME into this format m/d/y but the code below is not working, returns 12/31/1969 instead can someone show me how to do this?

我正在尝试将 mysql DATETIME 转换为这种格式 m/d/y 但下面的代码不起作用，返回 12/31/1969 而不是有人可以告诉我如何做到这一点？

$fromMYSQL = '2007-10-17 21:46:59'; //this is the result from mysql DATETIME field
echo date("m/d/Y", $fromMYSQL);

回答by Doug Hays

I think what you really want is this:

我认为你真正想要的是：

$fromMYSQL = '2007-10-17 21:46:59';
echo date("m/d/Y", strtotime($fromMYSQL));

The second argument of date is a timestamp and I think what is happening is PHP sees your string as a -1 timestamp... thus 12/31/1969.

date 的第二个参数是时间戳，我认为发生的事情是 PHP 将您的字符串视为 -1 时间戳......因此是 12/31/1969。

So, to get a timestamp from the string version of the date, you use strtotime

因此，要从日期的字符串版本中获取时间戳，请使用strtotime

回答by moo

SQL:

查询语句：

SELECT whatever, UNIX_TIMESTAMP(date) date FROM table WHERE whatever

PHP:

PHP：

date('m/d/Y', $result['date']);

回答by Joerg

The best way is to convert the dateformat direct in your Querystring:

最好的方法是直接在查询字符串中转换日期格式：

$TimeFormat = "%m/%d/%Y";  // your pref. Format

$sql = "SELECT DATE_FORMAT(DateCol , '" . $TimeFormat . "') as ConvertDate FROM tblTest";

Otherwise you can modify this function to your needs:

否则，您可以根据需要修改此函数：

function format_date($original, $format) {
    if (empty($original)) {
        $original = date("Y-m-d H:i:s");
    }
    $original = ereg_replace("30 Dez 1899", "30-01-1973", $original);
    $format = ($format=='date' ? "%m-%d-%Y" : $format);
    $format = ($format=='germandate' ? "%d.%m.%y" : $format);
    $format = ($format=='germandaydate' ? "%A, %d.%m.%Y" : $format);
    $format = ($format=='germantime' ? "%H:%M" : $format);
    $format = ($format=='germandatetime' ? "%d.%m.%y %H:%M:%S" : $format);
    $format = ($format=='datetime' ? "%m-%d-%Y %H:%M:%S" : $format);
    $format = ($format=='mysql-date' ? "%Y-%m-%d" : $format);
    $format = ($format=='mysql-datetime' ? "%Y-%m-%d %H:%M:%S" : $format);
    $format = ($format=='mssql-date' ? "%Y%m%d" : $format);
    $format = ($format=='mssql-datetime' ? "%Y%m%d %H:%M:%S" : $format);
    $format = ($format=='Ymd' ? "%Y-%m-%d" : $format);
    return !empty($original) ? strftime($format, strtotime($original)) : "";
}

回答by MitMaro

You need a capital Yint the date format string. the lowercase ygives a two digit year and the upper case 'Y' give a four digit year.

您需要一个大写的Yint 日期格式字符串。小写字母y给出两位数的年份，大写字母“Y”给出四位数字的年份。

$fromMYSQL = '2007-10-17 21:46:59';
echo date("m/d/Y", strtotime($fromMYSQL));

PHP Manual Page For datemay be of some assistance.

PHP Manual Page For date可能会有所帮助。

回答by Rob

I covered this in my answer to http://stackoverflow.com/questions/499014/i-need-to-change-the-date-format-using-php/499021#499021- basically, date()expects a Unix timestamp from which it calculates the date/time and formats that. You're passing in a string which comes from the MySQL query result, which probably gets mangled by PHP's duck typing into something that looks like a Unix timestamp, but makes no sense.

我在我对 h ttp://stackoverflow.com/questions/499014/i-need-to-change-the-date-format-using-php/499021#499021 的回答中介绍了这一点- 基本上，date()期望从中得到一个 Unix 时间戳它计算日期/时间并格式化它。您正在传入一个来自 MySQL 查询结果的字符串，该字符串可能会被 PHP 的鸭子输入看起来像 Unix 时间戳的东西所破坏，但没有任何意义。

I explain a couple of approaches to dealing with date columns in my answer.

我在回答中解释了几种处理日期列的方法。

回答by Larry K

Two issues:

两个问题：

1) You need a capital Y

1) 你需要一个大写的 Y

2) You need a correct date type.

2）您需要正确的日期类型。

Try

尝试

$fromMYSQL = date_create  ('2007-10-17 21:46:59');    
echo date("m/d/Y", $fromMYSQL);

Oops, strtotime is the right conversion to a timestamp. date_create returns a DateTime object.

糟糕， strtotime 是对时间戳的正确转换。date_create 返回一个 DateTime 对象。