SELECT Rows that match the current date and last 15 minutes.

jamjam

Please help.

I have a datetime field on a MySQL table.

I want to select rows that have both the current date and a time of last 15 minutes.

I have tried

SELECT * FROM main WHERE date_time >= DATE(NOW()) AND date_time > (NOW() - INTERVAL 15 MINUTE)

But this doesn't work

Thanks for your help in advance.

dalecosp

What is the content of the field in question? How can the same field be two different values?

jamjam

The field contains datetime values like 2010-11-04 20:00:00 or 2010-11-05 21:00:00 or 2010-11-06 10:00:00 and so on.

I want to SELECT the records that match the current date using the first part of the date_time field and also check the record against the current time to match 15 minute intervals.

Hope that's more clear.

bradgrafelman

This:

date_time >= DATE(NOW()) AND date_time > (NOW() - INTERVAL 15 MINUTE)

can simply be reduced to:

date_time > (NOW() - INTERVAL 15 MINUTE)

since any datetime that is greater than 15 minutes ago is of course greater than the current datetime as well.

Also, is the date_time column an actual DATETIME column, or is it a VARCHAR/etc. column?

dalecosp

jamjam;10967504 wrote:
The field contains datetime values like 2010-11-04 20:00:00 or 2010-11-05 21:00:00 or 2010-11-06 10:00:00 and so on.

I want to SELECT the records that match the current date using the first part of the date_time field and also check the record against the current time to match 15 minute intervals.

Hope that's more clear.

Well, it answers Brad's question ... that is standard MySQL DATETIME format.

The records selected then have these requirements?

Today's date.
Not older than 15 minutes.

Is that right? "to match 15 minute intervals" still doesn't register in my brain.

bradgrafelman

dalecosp;10967509 wrote:
Well, it answers Brad's question

Eh... Not really; the value of a column doesnt indicate which type it is.

jamjam

That's correct

Today's date.
Not older than 15 minutes.

Much clearer.
I used the term "15 minute interval" as I plan to eventually run a script every 15 mintues.

bradgrafelman the field is datetime field and not a varchar.

jamjam

By the way

This works in selecting records from the last 15 minutes

date_time > (NOW() - INTERVAL 15 MINUTE)

But it doesn't care about the date.

It will select all records within the last 15 minuted from all dates.

dalecosp

<?php
$mydt=date('Y-m-d H:i:s',strtotime('15 minutes ago'));
$sql="SELECT * FROM main WHERE date_time > $mydt";
?>

jamjam

Sorry for the late reply. I have only just noticed the solution above.

Dalecosp your solution doesn't seem to work for me.

But even if it did, I would not have used it, as I need a pure SQL solution and no PHP included.

This is because I plan to run a schedule procedure in my MySQL application and not a PHP script as I might have incorrectly suggested earlier.

Thanks for the help again.

jamjam

Worked it out

I pretty much used my original query

SELECT * FROM main WHERE date = DATE(NOW()) AND time > (NOW() - INTERVAL 15 MINUTE)

The only change I made was to create two fields in my table for date and time instead of the the single date_time field.

Now it works as I had hoped.