mysql UNIX_TIMESTAMP returns incorrect value

Question

I have the following date in mysql stored in DATETIME field:

2012-04-16 16:21:54

Than I select it like this:

SELECT UNIX_TIMESTAMP(date) ....

but if I print the hour using php date function I get one hour difference:

echo date("H:i:s", $row->date)

would print:

17:21:54

Without the second parameter, the date function returns correct value. Any ideas?

date() function will convert the date to the server time before displaying it. The one hour difference could be the expected result. — Salman Arshad
– Salman Arshad, Commented Apr 16, 2012 at 11:28
are the database and web server same? If they are different, do they have same timezones? — Salman Arshad
– Salman Arshad, Commented Apr 16, 2012 at 11:41

Ankit Sharma · Accepted Answer · 2012-04-16 14:33:39Z

3

According to mysql manual -

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. 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. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 10.6, “MySQL Server Time Zone Support”.

edited Apr 16, 2012 at 14:33

Ankit Sharma

4,0792 gold badges31 silver badges50 bronze badges

answered Apr 16, 2012 at 11:38

Ashim Saha

1624 bronze badges

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

3 Comments

Salman Arshad Over a year ago

How does this answer the question?

Ashim Saha Over a year ago

i made the line in bold. that explains the reason.

King Julien Over a year ago

Thanks, that's the correct answer. Running SET @@global.time_zone = 'America/New_York'; query solve the problem!

Douwe de Haan · Accepted Answer · 2012-04-16 11:28:05Z

0

with adding 'e' you can get the timezone for that timestamp.

It has probably something to do with different timezones. Try using http://www.php.net/manual/en/function.date-timezone-set.php.

If that won't work, explore the rest of the date function: http://php.net/manual/en/book.datetime.php

answered Apr 16, 2012 at 11:28

Douwe de Haan

6,7263 gold badges35 silver badges53 bronze badges

Collectives™ on Stack Overflow

mysql UNIX_TIMESTAMP returns incorrect value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related