Formatting an SQL timestamp with PHP

问题:

I have a mySQL database with a timestamp field. It currently only has one entry while I'm testing, it is

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.

回答1:

The date function expects an UNIX timestamp as 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 :

$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 date function ; that string is not a valid UNIX Timestamp.

'12-31-69' is probably 1970-01-01, in your locale ; and 1970-01-01 is the Epoch -- the date that corresponds to the 0 UNIX Timestamp.

回答2:

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 source on that issue.

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...

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.

回答3:

You could just use MySQL's date_format() function instead:

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.

回答4:

i think this will be useful to newble:

example basic subtraction 1 hour from date from MYSQL format:

$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

回答5:

EDIT: After checking, it appears that MySQL returns a timestamp as a string to PHP, so this answer was bogus :)

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.

回答6:

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

in mysql query i did:

            unix_timestamp(creationDate) AS creationDate
            unix_timestamp(editDate) AS editDate

in php convert i did:

    $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..