We help IT Professionals succeed at work.

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

jusmeig
jusmeig asked
on
Medium Priority
269 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

theGhost_k8Database Consultant
CERTIFIED EXPERT
Commented:
Is this what you do?
SELECT unix_timestamp( '2009-04-03' );
select from_unixtime(1238731200);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?



Author

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
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

Author

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 :)

Author

Commented:
Problem was identified, I found the workaround but the guys certainly helped!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.