I am trying to display some records from a database on a chart. The records saved within one minute is usually about 70 records. That number of records would look insane on a line chart, yet it would be more insane and unrealistic to expect users to keep requesting to view records per minute when their data interest is between a four hours interval.
I decided to to group the records by minute, there I got stuck in the query. Below is my query sample:
SELECT DISNTINCT YEAR(recievedon), MONTH(recievedon), DAY(recievedon), HOUR(recievedon), referenceprice
FROM dbname
WHERE recievedon >= '2015-06-05 10:30' AND recievedon <= '2015-06-05 10:50'
ORDER BY recievedon
How do I group the records by minute either using GROUP BY or DISTINCT
Secondly, is there a better way to render a large dataset in a (line) chart instead of grouping the records?
If I were to guess that you were using MySQL, then you can do use to_seconds(). The following gives the average reference price for each minute, along with the date/time of the first price in the interval:
select min(recievedon), avg(referenceprice)
from dbname
where recievedon >= '2015-06-05 10:30' AND recievedon <= '2015-06-05 10:50'
group by floor(to_seconds(receivedon) / 60)
EDIT:
In SQL Server, you can do:
select min(receivedon), avg(referenceprice)
from dbname
where recievedon >= '2015-06-05 10:30' AND recievedon <= '2015-06-05 10:50'
group by datediff(minute, 0, receivedon);
If you want the beginning of the period rather than the earlier timestamp:
select dateadd(minute, 0, datediff(minute, 0, receivedon)) as timeperiod,
avg(referenceprice)
from dbname
where recievedon >= '2015-06-05 10:30' AND recievedon <= '2015-06-05 10:50'
group by dateadd(minute, 0, datediff(minute, 0, receivedon)) ;