mybatis - how to get data for mysql query every 5min for sum(column) and last index value -

this image file sample data

first image query

2nd image want view

time value 00sec ~00sec

i mean 110000 ~110450 value" 04m:50s "

this exam data

for example sample data

data insert every 10sec.

## time    money amount  date hhmmss 100000 500 30 2016-07-28 ------------------------  100010 800 740 2016-07-28 -------------------------  100450 300 80 2016-07-28 ------------------------  100500 200 5 2016-07-28 -----------------------  100510 900 9144 2016-07-28 --------------------------   ~~~~~~~~~~~~~~~~~~~~~~~~   110000 500 5 2016-07-28 -----------------------  110500 233 5 2016-07-28 ======================= 

i want each 5min data sum , view

this query every 1min data

how change easy yo use

select sum(amount),        lpad(cast(time - 5  char),6,'0') dateval,        substr(lpad(cast(time - 5  char),6,'0'),1,4),        date,        max(time) example date = '2016-07-28' group substr(dateval, 1, 4) order dateval asc; 

and want data this

amountsum   time(range)         money   888         100000 ~100450      want last insert money value 44200 95          100500 ~100950      want last insert money value 44200 22          101000 ~101450      want last insert money value 44200 6843        101500 ~101950      want last insert money value 44200   2213        102000 ~102450      want last insert money value 44200   sequence time    money  mpare sel    buy   amount  date 1875    082100  710,000 0   710,000 702,000 27  2016-07-29 1874    082710  710,000 0   710,000 702,000 22  2016-07-29 1873    082730  710,000 0   710,000 702,000 1   2016-07-29 1877    090030  710,000 0   711,000 710,000 848 2016-07-29 1876    090100  711,000 1,000   711,000 710,000 2   2016-07-29 1884    090110  711,000 1,000   711,000 710,000 66  2016-07-29 1883    090120  710,000 0   711,000 710,000 5   2016-07-29 1882    090130  711,000 1,000   711,000 710,000 53  2016-07-29 1881    090140  710,000 0   711,000 710,000 11  2016-07-29 1880    090150  710,000 0   711,000 710,000 2   2016-07-29 1879    090200  710,000 0   711,000 710,000 10  2016-07-29 1878    090210  710,000 0   711,000 710,000 10  2016-07-29 1889    090220  712,000 2,000   712,000 711,000 313 2016-07-29 1888    090230  712,000 2,000   712,000 711,000 21  2016-07-29 1887    090240  712,000 2,000   712,000 711,000 4   2016-07-29 1886    090250  712,000 2,000   712,000 711,000 2   2016-07-29 1885    090300  713,000 3,000   713,000 712,000 25  2016-07-29 1894    090310  713,000 3,000   714,000 713,000 13  2016-07-29 1893    090330  714,000 4,000   714,000 713,000 13  2016-07-29 1892    090350  713,000 3,000   714,000 713,000 3   2016-07-29 1891    090400  714,000 4,000   714,000 713,000 1   2016-07-29 1890    090410  714,000 4,000   714,000 713,000 49  2016-07-29 1900    090420  715,000 5,000   715,000 714,000 27  2016-07-29 1899    090430  716,000 6,000   716,000 715,000 46  2016-07-29 1898    090440  716,000 6,000   716,000 715,000 86  2016-07-29 1897    090450  716,000 6,000   716,000 715,000 59  2016-07-29 1896    090500  716,000 6,000   716,000 715,000 3   2016-07-29 1895    090510  716,000 6,000   716,000 715,000 2   2016-07-29 1906    090520  717,000 7,000   717,000 716,000 15  2016-07-29 1905    090530  717,000 7,000   717,000 716,000 31  2016-07-29 1904    090540  717,000 7,000   717,000 716,000 3   2016-07-29 1903    090550  716,000 6,000   717,000 716,000 77  2016-07-29 

not sure result want, try this:

select      sum(amount) amountsum,     concat(min(`time`), '~', max(`time`)) `time(range)`,     `date` example cross join (     select min(`time`) mintime example `date` = '2016-07-28' ) t `date` = '2016-07-28' group truncate((time_to_sec(`time`) - time_to_sec(`mintime`)) / 60 / 5, 0) order max(`time`) asc; 

demo here