使用 PHP 格式化 SQL 时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2303724/
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
Formatting an SQL timestamp with PHP
提问by Chris Sobolewski
I have a mySQL database with a timestamp field. It currently only has one entry while I'm testing, it is
我有一个带有时间戳字段的 mySQL 数据库。它目前只有一个条目，而我正在测试，它是
2010-02-20 13:14:09
I am pulling from the database and using
我从数据库中提取并使用
echo date("m-d-Y",$r['newsDate'])
My end result is showing as
我的最终结果显示为
12-31-69
Anyone know why?
有谁知道为什么？
Edit: editedit: disregard that edit... the FTP addon for notepad++ timed out and unfortunately doesn't display an error when it can't synch.
编辑：editit：忽略该编辑...记事本++的FTP插件超时，不幸的是当它无法同步时不会显示错误。
回答by Pascal MARTIN
The datefunction expects an UNIX timestampas its second parameter -- which means you have to convert the date you get from the DB to an UNIX timestamp, which can be done using strtotime:
该date函数需要一个UNIX 时间戳作为其第二个参数——这意味着您必须将从数据库获得的日期转换为 UNIX 时间戳，这可以使用strtotime以下方法完成：
$db = '2010-02-20 13:14:09';
$timestamp = strtotime($db);
echo date("m-d-Y", $timestamp);
And you'll get :
你会得到：
02-20-2010
You were passing the '2010-02-20 13:14:09'string to the datefunction ; that string is not a valid UNIX Timestamp.
您正在将'2010-02-20 13:14:09'字符串传递给date函数；该字符串不是有效的 UNIX 时间戳。
'12-31-69' is probably 1970-01-01, in your locale ; and 1970-01-01is the Epoch -- the date that corresponds to the 0 UNIX Timestamp.
'12-31-69' 可能是1970-01-01，在您的语言环境中；和1970-01-01是纪元——对应于 0 UNIX 时间戳的日期。
回答by Urda
For starters, the php date()function is expecting seconds as the second variable. So that accounts for why your date is displaying wrong. Check this sourceon that issue.
对于初学者来说，phpdate()函数期望秒作为第二个变量。这就是为什么您的日期显示错误的原因。检查这个问题的来源。
Which then provides us the answer to the problem, to get PHP to format the date from a SQL timestamp correctly, we just change the query a tad...
然后为我们提供了问题的答案，为了让 PHP 从 SQL 时间戳正确格式化日期，我们只需稍微更改查询...
SELECT author, `when`
Change it to...
将其更改为...
SELECT author, UNIX_TIMESTAMP(`when`)
Then use the PHP date function, with the variable that is storing the result of that above SQL query.
然后使用 PHP 日期函数，以及存储上述 SQL 查询结果的变量。
回答by Marc B
You could just use MySQL's date_format()function instead:
你可以使用 MySQL 的date_format()函数来代替：
SELECT date_format(timestampfield, '%m-%d-%Y') FROM table etc....
This will save you having to round-trip your timestamp into unix time and then back into a normal date string in PHP. One datetime formatting call rather than two.
这将使您不必将时间戳往返转换为 unix 时间，然后返回到 PHP 中的正常日期字符串。一个日期时间格式调用而不是两个。
回答by qant
i think this will be useful to newble:
我认为这对 newble 很有用：
example basic subtraction 1 hour from date from MYSQL format:
从 MYSQL 格式的日期起 1 小时的基本减法示例：
$to='2013-25-10 22:56:00'; //curr time
$timestamp = strtotime($to); //convert to Unix timestamp
$timestamp = $timestamp-3600; //subtract 1 hour (3600 this is 1 hour in seconds)
echo date("Y-m-d H:i:s",$timestamp); //show new date
回答by James Van Leuvaan
ok, I was wrestling with this for a week (longer but i took a break from it).
好吧，我为此挣扎了一个星期（更长的时间，但我休息了一下）。
I have two specific fields in tables
我在表中有两个特定字段
creationDate > timestamp > current_timestamp editDate > timestamp > current_timestamp
they were pulling out either dec 31 1969, or just nothing... annoying... very annoying
他们要么在 1969 年 12 月 31 日退出，要么就什么都没有……烦人……非常烦人
in mysql query i did:
在 mysql 查询中我做了：
unix_timestamp(creationDate) AS creationDate
unix_timestamp(editDate) AS editDate
in php convert i did:
在 php 转换中我做了：
$timestamp = $result_ar['creationDate'];
$creationDate = date("Y-M-d (g:i:s a)", $timestamp)
echo($creationDate);
$editstamp = $result_ar['editDate'];
$editDate = date("Y-M-d (g:i:s a)", $editstamp)
echo($editDate);
this solved my problem for me returning
这解决了我回来的问题
2010-Jun-28 (5:33:39 pm)
2010-Jun-28 (12:09:46 pm)
respectively.
分别。
I hope this helps someone out..
我希望这可以帮助某人..
回答by Andomar
EDIT: After checking, it appears that MySQL returns a timestampas a string to PHP, so this answer was bogus :)
编辑：检查后，MySQL 似乎将 atimestamp作为字符串返回给 PHP，所以这个答案是假的 :)
Anyway, the reason you get a date in 1969 is probably that you're converting a zero unix time from UTC to localtime. The unix time is the number of seconds since 1970. So a value of 0 means 1970. You probaby live in a timezone with a negative offset, like GMT-6, which ends up being 31-12-69.
无论如何，您在 1969 年获得日期的原因可能是您将 UTC 时间为零的 unix 时间转换为本地时间。unix 时间是自 1970 年以来的秒数。所以值 0 表示 1970 年。你可能生活在一个负偏移的时区，比如 GMT-6，最终是 31-12-69。
