My SQL timestamp returns a timestamp in the past? Incorrect timestamp?

Hi there,

If I run the command below in php my admin:
SELECT unix_timestamp( '2009-04-03' );

I get the following timestamp: 1238713200
If I convert this timestamp to a date it is: Thu, 02 Apr 2009 23:00:00 GMT

This is obviously incorrect? I need to run the above and get the timestamp for 2009-04-03 00:00:00. Am I doing something wrong?

Cheers
jusmeigAsked:
Who is Participating?
 
Lukasz ChmielewskiCommented:
It loses hours due to conventions for local time zone changes,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
0
 
theGhost_k8Database ConsultantCommented:
Is this what you do?
SELECT unix_timestamp( '2009-04-03' );
select from_unixtime(1238731200);

0
 
jusmeigAuthor Commented:
If I do what you say, this is what I get:
SELECT unix_timestamp( '2009-04-03' ); - 1238713200
select from_unixtime(1238713200); - 2009-04-03 00:00:00

But if I convert the timestamps here using: http://www.onlineconversion.com/unix_time.htm
I get: Thu, 02 Apr 2009 23:00:00 GMT

The timestamp php generates for 2009-04-03 = 1238716800

So when I try to do a comparison there is no match, and my program breaks! Is it the PHP timezone that is wrong?



0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
jusmeigAuthor Commented:
Hi there,

I saved my dates as a date.
I guess I could just compare the value of the date and my php date as a string match?
IE match yyyy-mm-dd as a string match, this will get me around time zones etc?

J
0
 
Lukasz ChmielewskiCommented:
yes, but comparing like this will not tell you that some is smaller or larger (in time). if it does match, than it does match.

try with UTC_TIMESTAMP in mysql
0
 
jusmeigAuthor Commented:
Not interested in the time, just the date.
Roads_Road u get some of the beans.....as I guess you mentioned what was wrong....but the workaround was mine :)
0
 
jusmeigAuthor Commented:
Problem was identified, I found the workaround but the guys certainly helped!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.