2

Note: Solution at the end of the question

I have accumulated energy data every 15min in my table. I need to query this data to get the energy spent every hour of the current day. To do so, I need to select the value of every hour and substract it from the value of the previous one, as shown in the picture.

DB

With the following code I've already selected the hourly data, as shown in the result below:

SELECT DATE_FORMAT(FechaHora, '%Y-%m-%d %H:00:00')as Horas, FechaHora, Pan_kWh_im
FROM gaia_piloto1 T1
JOIN (
    SELECT DATE_FORMAT(FechaHora, '%Y-%m-%d %H:00:00') AS Horas,     MAX(FechaHora) AS MaxFH
    FROM gaia_piloto1
    WHERE FechaHora BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59')
    GROUP BY Horas) T2
ON T1.FechaHora = T2.MaxFH

Intermediate Result

Now I need to add some more code to do the substraction of consecutive rows. How can I do it?

Thank you in advance for all your help,

SOLUTION: Thanks to the suggestion made in the comments, I came with this solution, maybe not optimal but suits my needs:

SELECT T4.Horas, T4.kWh_hr
FROM (
    SELECT T3.Horas, T3.FechaHora, T3.Pan_kWh_im, (T3.Pan_kWh_im - @lastValue) AS kWh_hr, @lastValue:=T3.Pan_kWh_im AS Calc
    FROM (SELECT @lastValue := 0 ) initvars,
          ( SELECT DATE_FORMAT(FechaHora, '%Y-%m-%d %H:00:00')as Horas, FechaHora, Pan_kWh_im
                FROM gaia_piloto1 T1
                JOIN (  (SELECT FechaHora AS Horas, FechaHora AS MinFH
                            FROM gaia_piloto1
                            WHERE FechaHora <= DATE_FORMAT(NOW() - INTERVAL 1 Day,'%Y-%m-%d 23:01:00')
                            ORDER BY Horas DESC
                            LIMIT 1)
                            UNION
                            (SELECT DATE_FORMAT(FechaHora, '%Y-%m-%d %H:00:00') AS Horas, MIN(FechaHora) AS MinFH
                            FROM gaia_piloto1
                            WHERE FechaHora BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59')
                            GROUP BY Horas
                            ORDER BY Horas DESC)
                        ) T2
                ON T1.FechaHora = T2.MinFH
                ORDER BY FechaHora ) T3
    ) T4
WHERE Horas >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')
Julian TM
  • 21
  • 3
  • Please, post some sample of table extract: `SELECT * FROM gaia_piloto1 ORDER BY FechaHora DESC LIMIT 15` – F. Hauri - Give Up GitHub May 04 '16 at 16:38
  • First I would add a row number to the intermediate result table. See: http://stackoverflow.com/questions/5351628/how-can-i-add-a-new-column-which-counts-the-number-of-rows-as-serial-number And once the row number is added then create a query against the intermediate results that subtracts the n-1 value. – mba12 May 04 '16 at 16:38
  • Possible duplicate of [MySQL - Subtracting value from previous row, group by](http://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by) – ArturoAP May 04 '16 at 16:44
  • Thank you so much for your answers! All of them helped me to get a solution to my needs, although perhaps not the best one, as I'm new to SQL. I edited the post to reflect the solution I came with, should it help someone else in the future. It's an integration of few methods I found through your suggestions. Regards – Julian TM May 06 '16 at 21:23

0 Answers0