-1

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!

eggyal
  • 122,705
  • 18
  • 212
  • 237
Lonnie Best
  • 9,936
  • 10
  • 57
  • 97
  • 1
    http://codepad.viper-7.com/RFHhWL It works for me. I think there is an issue when pulling the date from your database, perhaps it cuts off at the space character. Can you try echoing the $row[8] variable? – Jared May 31 '12 at 17:52
  • What does your query look like? When I run `$epochTime = strtotime('2004-11-03 06:29:48'); echo $epochTime;` I receive a correct value of `1099484988` as expected. – Andy May 31 '12 at 17:55
  • @Andy And I get `1099463388` for the same line in your comment. I suspect locale settings are affecting the result. – Izkata May 31 '12 at 18:01
  • Which version of PHP are you running? Which timezone do you have set? – MetalFrog May 31 '12 at 18:02
  • 2
    @Andy Converting `'2004-11-03 06:29:48 GMT'` results in what I got, converting `'2004-11-03 06:29:48 CST'` results in what you got. – Izkata May 31 '12 at 18:03
  • That said, the finale few digits between `GMT` and `CST` are the same because minutes and seconds are the same (only hours have shifted). So LonnieBest seems to have another issue was well as time zone. – Izkata May 31 '12 at 18:04
  • @Izkata Good point. Mine was in CST. – Andy May 31 '12 at 18:04
  • What code are you using to grab the datetime from the database? – Jared May 31 '12 at 18:33

1 Answers1

3

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?