Page 1 of 1

"Resetting" Dates based on seconds

Posted: Wed Dec 08, 2010 3:18 pm
by btop
Hey Guys,
I'm working on an auction site that uses datetime fields in MySQL to determine the time left in an auction. Time is added to the auction however, each time a bid is placed.

The rules are every bid adds 1 second to the timer. If the timer is under 15 seconds, a bid resets the timer to 15 seconds.

The problem we're facing is that often the timer will go past 15 seconds if heavy bids are being placed, instead of "resetting" to 15 seconds. Here's what we currently have:
UPDATE `auctions` SET end_dt =
IF(TIME_TO_SEC(TIMEDIFF(end_dt,'".now()."')) <= 15,
DATE_ADD(STR_TO_DATE(end_dt,'%Y-%m-%d %H:%i:%s'),
INTERVAL (15 - TIME_TO_SEC(TIMEDIFF(end_dt,'".now()."'))) SECOND),
DATE_ADD(STR_TO_DATE(end_dt,'%Y-%m-%d %H:%i:%s'), INTERVAL 1 SECOND))
WHERE id = '".$id."'
The end_dt is the date we use to determine the time left in an auction (or seconds left). The now() is a function that simply supplies a mysql datetime based off a specified time zone - so it's an equivalent to mysql's now(), just set for specific local.

Any help would be really appreciated!

Re: "Resetting" Dates based on seconds

Posted: Thu Dec 09, 2010 9:31 am
by VladSun
It should be much more simplier :)

Code: Select all

UPDATE `auctions` SET 
	end_dt = 
		IF (timestampdiff(SECOND, end_dt, now()) <= 15),
			now() - INTERVAL 15 SECOND, 
			end_dt + INTERVAL 1 SECOND
		)

Re: "Resetting" Dates based on seconds

Posted: Thu Dec 09, 2010 9:45 am
by pickle
datetime is usually better for storing dates, but in your case I'd consider using UNIX timestamps. If you're going to be doing this query over and over, using a UNIX timestamp will make that query much simpler.

Re: "Resetting" Dates based on seconds

Posted: Thu Dec 09, 2010 10:02 am
by VladSun
pickle +1

another way to write it:

Code: Select all

UPDATE `auctions` SET 
		end_dt = LEAST
			(
				now() - INTERVAL 15 SECOND, 
				end_dt + INTERVAL 1 SECOND
			)
This way it's as smiple as if it was timestamp type.