[SOLVED] php or mysql for this query?

[chiprivers]

I have a database table 'selections' which consists of various fields including 'startTime' in datetime format (YYYY-MM-DD HH:MM:SS).

 

I am writing a script that will be running automatically every so often and I need it to pull a record from the above table where the record is the next record with a 'startTime' within the next 10 minutes.

 

I am not sure on the best way to construct the query, using either php or mysql, without having to mess around with the date format too much.  I am not that familiar with the mysql functions but I think that would be the easiest way.  I have this much:

 

SELECT * FROM selections WHERE startTime ...(in the next 10 minutes)...ORDER BY startTime ASC LIMIT 1

 

Just not sure on the middle bit  ???

[corbin]

MySQL can't internally handle text dates (well, in this case, I'm assuming), but php can (strtotime).  The problem is, it would be a huge performance hit if you pulled all the data and it was a bunch of rows....

 

I would suggest using something like a unixtimestamp or one of mysql's built in date fields for this....

 

That way comparing them would be easy, and when ever you wanted to show them in a common format you could just use date(format, timestamp).

[chiprivers]

I would suggest using something like a unixtimestamp or one of mysql's built in date fields for this....

 

I have used datetime, mysqls built in date and time data type ?!

[per1os]

You can always create the time in the format you need.

 

www.php.net/date

 

<?php
$time = date('Y-m-d h:i:s', time()+60*10); // next 10 minutes

?>

 

Than insert that into the query for the check, do not quote me on the format though look at how to correctly code for the one you need at the www.php.net/date site.

[Barand]

SELECT * FROM selections WHERE startTime BETWEEN NOW() AND NOW() + INTERVAL 10 MINUTE ORDER BY startTime ASC LIMIT 1