[SOLVED] Strange results from query

[bsamson]

I have a table that looks like this:

 

ID      entryDate        storeNo

01    1225843575      5843

02    1225929544      5843

 

entrydate 1225843575 is 11/04/2008, and 1225929544 is 11/05/2008.

 

Now, when I run this query:

SELECT * FROM v2breakdown WHERE DATE_FORMAT(FROM_UNIXTIME(`entrydate`), '%m/%d/%Y') = '11/05/2008'

 

phpMyAdmin returns both id 01, and id 02. Is there something wrong w/ the query? It should only return ID 02.

 

Thanks in advance for any direction! Thanks!

[PFMaBiSmAd]

Execute the following two queries and tell us what you get -

 

SELECT FROM_UNIXTIME(1225843575)

 

SELECT FROM_UNIXTIME(1225929544)

 

 

[bsamson]

SELECT FROM_UNIXTIME(1225843575) = 2008-11-05 00:06:15

 

SELECT FROM_UNIXTIME(1225929544) = 2008-11-05 23:59:04

 

hhmmm ... im confused now. because in php if i run:

 

echo date("m/d/Y", 1225843575)."<br>".date("m/d/Y", 1225929544)

 

I get:

11/04/2008

11/05/2008

 

 

[PFMaBiSmAd]

The timezone setting of mysql is different than your php.

 

A Unix Timestamp has several problems and limitations with its use. If you use a mysql DATE or DATETIME, 2008-11-05 will always be 2008-11-05. It won't care what the server's clock is or what timezone setting is being used to convert a Unix Timestamp into a date/time value.

[bsamson]

Great! Thanks for your help.

 

It's a new dedicated server and the timezone was incorrect initially. When I set it correctly it did not update mysql. After restarting MySQL all was well. Thanks again!