MySQL UNIX_TIMESTAMP acting weird

Question

In my database I have a datetime field with the following value 2014-07-21 00:00:00.

When I pull the data I have the line:

SELECT UNIX_TIMESTAMP(date) AS `date` ...

When I then use PHP date to format it for human reading the following happens:

echo date('d/m/Y H:i:s', $row['date']);
// outputs 20/07/2014 23:00:00

I don't understand what's doing this.

Probably timezones and/or daylight savings. Check that your PHP and MySQL timezone settings and configs are the same. — ceejayoz
– ceejayoz, Commented Jul 11, 2014 at 15:40
why don't you use simply SELECT DATE_FORMAT(date, '%d/%m/%Y %H:%i:%s') instead? — VMai
– VMai, Commented Jul 11, 2014 at 15:43
@VMai: because in the object that's loading the data I want it as a timestamp. That way I can control the presentation format with PHP for different scenarios. — diggersworld
– diggersworld, Commented Jul 11, 2014 at 15:59
I thought so. Maybe you could edit this restriction into your question. — VMai
– VMai, Commented Jul 11, 2014 at 16:22

deceze · Accepted Answer · 2014-07-11 15:43:07Z

4

It's a timezone problem. 2014-07-21 00:00:00 will convert to very different UNIX timestamps depending on what timezone the database assumes this time to be in. Conversely, PHP will convert the UNIX timestamp to the human-readable version depending on the timezone set with date_timezone_default_set. You'll have to ensure that MySQL's internal timezone setting and PHP's internal timezone setting are identical if you expect the same value to be output.

answered Jul 11, 2014 at 15:43

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

diggersworld Over a year ago

Cheers, that sounds about right, I'll accept when the time limits done.

1stmajor · Accepted Answer · 2014-07-11 16:08:26Z

0

I replicated the same in my database and I got exactly the same value. I cannot say if you and I are in the same timezone, but one thing is sure - according to the documentation Here

If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC

That explains the disparity. To solve the problem you can select the date normally and use strtotime in php to convert it to timestamp. As in

SELECT date AS `date` ...
echo date('d/m/Y H:i:s', strtotime($row['date']));

Hope this helps..cheers!

answered Jul 11, 2014 at 16:08

1stmajor

112 bronze badges

Collectives™ on Stack Overflow

MySQL UNIX_TIMESTAMP acting weird

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related