PHP: Can't Convert MySQL DateTime to Epoch (with Correct Time of Day)

Question

I'm grabbing a datetime from a MySQL database using php.

In mysql the datetime looks like this:

'2004-11-03 06:29:48'

The line of my code that get the datetime from the database looks like this:

$epochTime = strtotime($row[8]);

Php's strtotime function convert this time to the wrong epoch time:

1099461600

That's incorrect. If you convert this back using http://www.epochconverter.com/ you can see that the time of day that was originally in mysql has been lost:

Wed, 03 Nov 2004 06:00:00 GMT

How can I get an accurate epoch time that has a specificity that matches the datetime from mysql?

Edits:

I just discovered that mysql is only returning the date (without the time):

2004/11/03

I've tried to force it return the time using this:

select DATE_FORMAT(`FieldDateTime`,'%Y-%m-%d %k:%I:%s') from table where id=1;

But this didn't work either; it continues to only return the date (without the time).

Ack! I figured it out. I was specifying the wrong field in my array:

$epochTime = strtotime($row[8]);

Should have been:

$epochTime = strtotime($row[9]);

It turns out that $row[8] was a formatted date field too, causing my confusion. Thanks for the help!

Answer 1

You could try:

SELECT UNIX_TIMESTAMP(`timestamp_column_name`) AS `timestamp`;

Then in PHP:

echo date("Y-m-d H:i:s", $row['timestamp']);

Does this match MySQL?