Formatting an SQL timestamp with PHP

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.

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.

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.

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.