Formatting an SQL timestamp with PHP

Question

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.

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

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