figuring out when 2 hours have passed

[SCook]

Hi all,

 

What I'd like to do is this.  get a date and time and determine if it has been a certain amount of time since then.  For example, I make an entry in the database of 11:00pm, and I want to periodically check, and if it is 1:00am or later, then I would update the database.  Any suggestions?

[obsidian]

Well, the best way is to just run a CRON script every minute or so, and have that script check the database for anything that was posted more than 2 hours ago. This way, your two hour limit will always be found:

SELECT * 
FROM myTable
WHERE post_time <= SUBTIME(NOW(), INTERVAL 2 HOUR)
AND updated = 0

 

This query should return all records in the DB table that were created more than two hours ago. When you update them, you ought to set an updated flag (the AND in the query above) so that you don't double up your efforts.

[SCook]

That's very interesting.  Never ceases to amaze me how many things are still out there to learn, even when you think you've got it down.  I didn't know about that feature of mysql.

 

I was going to save a timestamp with getimeofday and then run a comparison.  However, your method would elminate a lot of calculation and time, computer time anyway, I think.

[cooldude832]

that will work, I have to ask conceptually why.  If you are only updating database info why force it to cron when it can happen on a user load, or not even deal with it at all and let the Date functions handle it.

[SCook]

Well, the reason for this is that it is part of a shopping system in which a user can add items to a wish list, not unlike a gift registery.  Then, you can go in and purchaqse items off this list for them.  Now, the problem is that if multiple users are browsing the list, we want the first one to select an item to eliminate it from the purchase list.  However, if for some reason the transaction is not completed within a given time, and thu,s the wish list item isn't permanently removed, it must be reset so that it is back on the purchaseable list.

 

You could probely perform this entirely with user input, and not a cron, but I think that because of the structure of the functionality, it needs to be checked periodically.

[cooldude832]

well i think what you need to do is is simply have a integer field for the items.

 

0) Item is on the list and unpicked

1) Item is on the list and is picked

2) Item is on the list and purchased

 

 

so when it goes in the temp list it changes to 1 and 2 when it is sold.  The trick to purge the items in 1) is to use cookies/sessions and link them to a user, each time the list is queried it checks the timestamp of the last move on that cookie/session if its too long then it moves them back to 0