Query to subtract same column value at different interval of day with SQL database

Question

In MySQL, I want to subtract one of column value at different interval of time based on another column 'timestamp'.

table structure is : 
id | generator_id | timestamp             | generated_value
1  |  1           | 2019-05-27 06:55:20   | 123456     
2  |  1           | 2019-05-27 07:55:20   | 234566       
3  |  1           | 2019-05-27 08:55:20   | 333456       
..
..    
20  |  1           | 2019-05-27 19:55:20   | 9876908

From above table I want to fetch the generated_value column value which should be difference of first timestamp fo day and timestamp of last value of day.

In above example I am looking query which should give me output as 9,753,452 (9876908 - 123456).

In general to fetch the single record of first value and last value of day I use below query

// Below will give me end day value
SELECT * FROM generator_meters where generator_id=1 and timestamp like '2019-05-27%' order by timestamp desc limit 1 ;

//this will give me last day value 
SELECT * FROM generator_meters where generator_id=1 and timestamp like '2019-05-27%' order by timestamp  limit 1 ;

Question is how should I get the final generated_value by doing minus of first value of day from last value of day.

Expected Output 
 generator_id | generated_value
  1           | 9753452

Thanks in advance !!

Check the Answers of this question: https://stackoverflow.com/questions/26817139/how-can-i-subtract-two-rows-values-within-same-column-using-sql-query — Randa ElBehery, May 27 '19 at 08:44
@mkRabbani Edited the question with DB Name as SQL, Thanks !! — par, May 27 '19 at 08:50

Thorsten Kettner · Accepted Answer · 2019-05-27T12:24:32.837

In your example the value gets bigger and bigger. If this is guaranteed to be so, you can use

select max(generated_value) - min(generated_value) as result
from sun_electric.generator_meters
where generator_id = 1
and date(timestamp) = date '2019-05-27';

Or for multiple IDs:

select generator_id, max(generated_value) - min(generated_value) as result
from sun_electric.generator_meters
and date(timestamp) = date '2019-05-27'
group by generator_id
order by generator_id;

If the value is not ascending, then you can use the following query for ID 1:

select last_row.generated_value - first_row.generated_value as result
from 
(
  select * 
  from sun_electric.generator_meters
  where generator_id = 1
  and date(timestamp) = date '2019-05-27'
  order by timestamp
  limit 1
) first_row
cross join
(
  select * 
  from sun_electric.generator_meters
  where generator_id = 1
  and date(timestamp) = date '2019-05-27'
  order by timestamp desc
  limit 1
) last_row;

Here is one way to get a result for multiple IDs:

select
  minmax.generator_id,
  (
    select generated_value 
    from sun_electric.generator_meters gm
    where gm.generator_id = minmax.generator_id
    and gm.timestamp = minmax.max_ts
  ) -
  (
    select generated_value 
    from sun_electric.generator_meters gm
    where gm.generator_id = minmax.generator_id
    and gm.timestamp = minmax.min_ts
  ) as result
from
(
  select generator_id, min(timestamp) as min_ts, max(timestamp) as max_ts
  from sun_electric.generator_meters
  where date(timestamp) = date '2019-05-27'
  group by generator_id
) minmax
order by minmax.generator_id;

You can also move the subqueries to the from clause and join them, if you like this better. Yet another approach would be to use window functions, available as of MySQL 8.

score 1 · Answer 2 · answered May 27 '19 at 09:16

This following script will return your expected results for the filtered ID and Date-

SELECT generator_id,CAST(timestamp AS DATE) ,
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = max(timestamp)
)
-
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = min(timestamp)
) AS Diff
FROM sun_electric.generator_meters 
WHERE generator_id = 1
AND CAST(timestamp AS DATE) = '2019-05-27'
GROUP BY generator_id,CAST(timestamp AS DATE) ;

If you want the same result with GROUP BY ID and Date just remove the filter as below-

SELECT generator_id,CAST(timestamp AS DATE) ,
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = max(timestamp)
)
-
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = min(timestamp)
) AS Diff
FROM sun_electric.generator_meters 
GROUP BY generator_id,CAST(timestamp AS DATE) ;

Query to subtract same column value at different interval of day with SQL database

2 Answers2