"Resetting" Dates based on seconds

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
btop
Forum Newbie
Posts: 2
Joined: Wed Dec 08, 2010 3:12 pm

"Resetting" Dates based on seconds

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: "Resetting" Dates based on seconds

Post 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
		)
Last edited by VladSun on Thu Dec 09, 2010 4:04 pm, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: "Resetting" Dates based on seconds

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: "Resetting" Dates based on seconds

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply