MySQL query and Time

[topflight]

Hello I have a table called called flights where it has all my flights info. But in that table I have a column called time. I want to put pull a random 15 flights who time is 30mins from the current time. For instance if the current time is 4:30 I want to pull a random 15 rows who time is 35 mins from now(Which is 4:30) how can I do that.

 

I have reccevied help, and someone provided the following query

 

select * from yourtable where timecolumn between date_format(now(),'%H%i') and addtime(date_format(now(),'%H%i'),30) order by rand() limit 15

 

So then I edit the code to look like this

 

<?php	include'config.php';	$get = mysql_query("SELECT * from `dep` WHERE dtime between date_format(now(),'%H%i') and addtime(date_format(now(),'%H%i'),30) order by rand() limit 15");?><table width="100%"><?while($row = mysql_fetch_assoc($get)){?><td><? echo "$row[dtime]";?></td><?php}?></table>

 

 

In the database the time does not have a ":" between the hours and mins, inseated they look like this 1730 instead of 1730 and the time is in 24 hour. Please help.

 

Thanks in advanced!

[schilly]

what type of field is dtime?

[topflight]

It is decimal(4,0) thats the way I reccevied it and I have 15,000 records so I which I can put ":" between the hour and min. will it still work tho?

[schilly]

not with that query. it is assuming you are using a datetime or timestamp field.

 

try

SELECT * from `dep` WHERE dtime >= $time_minus_thirty AND dtime <= $time_plus_thirty order by rand() limit 15

 

you going to need to create time_minus_thirty and time_plus_thirty by yourself using php.

[topflight]

I am confused how can I do that?

[schilly]

$hour = date('G');
$minute = date('i');

//add 30 to minutes. if its over 60 minus 60 off it and add one to hour. if hour is then over 23 set to 1. similar for minus 30 minutes.

//put your times together
$time_string = $hour . $minute;

//now use $time_string in your query.

[topflight]

I am starting to get it a little bit, can you please write the whole SQL with variables and everything please

[schilly]

Well I've already done the query and pretty much spelled out the whole thing so I believe it's up to you now. A little trial and error and you should have it.