asked on

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

ASKER CERTIFIED SOLUTION

SoLost

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

td234

ASKER

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.

SoLost

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'

td234

ASKER

Perfect. Thank you so much.