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