Mysql Query not giving me desired time result

Hi.

Okay, I am really frustrated on this one. I am using Tank Auth in Codeigniter. This has a query to see if the time/date in the data base is within 900 seconds of the current time before it resets a password. My problem is that the time in the DB can be within the time (900 seconds), but I am getting incorrect results. I suspect this has to do with a time zone issue as the "time" I am using to compare is generated by PHP before the query is sent and being compared to the unix time stamp in mySQL. Here is some output to show what is going on:

Database date: 2010-04-23 15:14:07
UNIX_TIMESTAMP of above: 1272050047

now time (time()):1272053977
now date:2010-04-23 15:19:37

compare time (time() - 900):1272053077
compare date:2010-04-23 15:04:37

DB time (1272050047) is NOT greater than compare time (1272053077). This is a problem.
But is DB date (2010-04-23 15:14:07) is greater than compare date (2010-04-23 15:04:37)

SELECT 1, UNIX_TIMESTAMP(new_password_requested) FROM (vmt_company_users) WHERE id = '1' AND new_password_key = '19f6b08c775d3d79535996aa4d60e351' AND UNIX_TIMESTAMP(new_password_requested) > 1272053077 LIMIT 1

How do I resolve this?

Thanks in advance for your help!!

Thom
LVL 2
td234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SoLostCommented:
How is "new_password_requested" stored in the table.  Is it DATETIME, TIMESTAMP?

If it is DATETIME, could you not do it all internally on the database and try this?

AND DATE_ADD(new_password, INTERVAL 900 second) > NOW()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
td234Author Commented:
HI.

Yes, it is datetime. I tried this and it still did not work.

SELECT 1, UNIX_TIMESTAMP(new_password_requested)
FROM (vmt_company_users)
WHERE id = '1'
AND new_password_key = '19f6b08c775d3d79535996aa4d60e351'
AND DATE_ADD(new_password_requested, INTERVAL 900 second) > NOW()
LIMIT 1

Seems to be an hour off. I can manually set the datetime and then run the query and it seems to go from success to failure around 3600.
0
SoLostCommented:
When you originally update or insert the "new_password_requested" in the table are you using the NOW() function or are you specifying it manually? e.g. '2010-04-23 01:02:03'
0
td234Author Commented:
Perfect. Thank you so much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.