frankenapps frankenapps - 2 years ago 101
MySQL Question

SQL: How to find datetime that contains specific minutes?

I have a database where a new entry gets added every minute.

I want to make a query where the result is in an intervall of 1 hour (e.g. 2016-06-19 20:01, 2016-06-19 21:01, ...).

This is my current approach (I used minute 42 of every hour, because that might only occur in the minutes part of the date):

SELECT * FROM 'raw_data' WHERE CONTAINS ('date', "42")>0

However I get an empty result, even so my test database looks like that:
My test Database

Does anyone know why I get an empty result, is it because I am using a String to compare with datetime?

Answer Source

You can try DATE_FORMAT():

SELECT * FROM 'raw_data' WHERE DATE_FORMAT('date', '%i') LIKE '42';

Or more precise you can use MINUTE() function:

SELECT * FROM 'raw_data' WHERE MINUTE('date') = 42;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download