SQL Server Return 4 rows for every hour in a day

Question

I have a query that returns all messages from a device within a day (simplified):

    SELECT date, value 
    FROM Messages 
    WHERE date between '04/01/2018 00:00:00' AND '04/01/2018 23:59:59' 
    ORDER BY date asc

The problem is that it returns too many rows. For example, 1 row per minute minimum (1440 rows in a day), and I have to print that in a chart.

How could I return the first row in every quarter hour so I get 4 rows per every hour of the day?

Expected result:

        date                value  
2018-01-04 05:00:00.000  ||  5,52
2018-01-04 05:15:00.000  ||  5,48
2018-01-04 05:30:00.000  ||  5,35
2018-01-04 05:45:00.000  ||  5,42

Answer 1

you can do it by a Modulus (%) like as follow:

SELECT date, value 
    FROM Messages 
    WHERE date between '04/01/2018 00:00:00' AND '04/01/2018 23:59:59'  and (datepart(minute,date) % 15) = 0
    ORDER BY date asc;

This query returns a data which contains a date row which minute completely divide with 15 (Quarter). I think this may solve your problem.

Note: I not used Seconds because of your data added per minute as per your language in question.