Data each minute, agrupate by each hour of the day

1

Dear colleagues,

I have a Mysql Table where I am registering each minute the value of a counter…
example : 14:01:00 (9080) 1
4:02:00: (10020)
4:03:00 (10250)…

I would like to have a graphic bar with the total of each hour, so , i suppose that I should get the first value of each hour and the last one … por example: 14:00:00 (9000) and 14:59:58 (13000) and substract 13000-9000 in order to have the total I have produced during that hour: 40000.

How can I do it for a bar with 24 bars? , I mean , one bar for each hour of the day…

Many thanks in advance!!

My actual trend:

image
image792×681 29.6 KB

2

Hi

Check this:

SELECT   
  DATEADD(HOUR, DATEDIFF(HOUR, 0, fechaini), 0) as time,
  namevar as metric,
  max(valuevar)-min(valuevar) as valuevar
  FROM FS_92_velocidades
  group by DATEADD(HOUR, DATEDIFF(HOUR, 0, fechaini), 0), namevar
3

Many thanks @deejayluk,

Being honest, I see and I achieve understand what you are trying to do, but I dont know what I can change in order to see the data…

Let me paste the result:

image
image1678×794 72.3 KB

4

My code is for Microsoft SQL Server connection, but you have MySQL.
I think dateadd is not recognized by MySQL.
Check this:

date_format(fechaini, ‘%Y-%m-%d %H:00:00’)

or check DATE_ADD

5


#function_date-add

Antony.