How to get interval value between two hours in mysql?

Posted on

Question :

I want to get value between 2 hours:

11:30:00 => 245.90
10:30:00 => 215.80
11:30:00 - 10:30:00 => 245.90 - 215.80 = 30.1 

and update data every hour.

UPDATE

I tried like this but didn’t work

SELECT(SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$cur_time' - SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$last_time') as kwh_avg

UPDATE1

This is my table

enter image description here

UPDATE2

I want to do subtraction between 12:36:01 – 11:36:01 and put it to my graph

Answer :

The most immediate reason your attempt did not work was because it was syntactically invalid.

You can use the result of a SELECT statement as a scalar value in an expression but you must enclose the said SELECT statement in parentheses. In your case, there are two such SELECT statements. Below is your attempted query slightly reformatted for readability with added comments to mark both of the SELECTs:

SELECT
  (
    /* this is SELECT #1 */
    SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$cur_time'
    -
    /* this is SELECT #2 */
    SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$last_time'
  ) as kwh_avg

Both SELECT #1 and SELECT #2 must be enclosed in parentheses individually, like this:

SELECT
  (
    /* SELECT #1 bracketed */
    (SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$cur_time')
    -
    /* SELECT #2 bracketed */
    (SELECT kwh FROM energy_logger WHERE DATE = '$current_date' AND HOUR(Time) = '$last_time')
  ) as kwh_avg

The parentheses around the entire expression are not necessary, but they are not harmful or invalid either and may actually help readability.

The syntactic issue is now out of the way, but given the nature of your data, the query would still not work. It would produce an error of a different kind, and here is why. A SELECT statement can be used in a scalar expression like that only if it returns no more than one value. It means, the statement must

  • select only one column, which is what you are doing correctly there,
    and
  • return no more than one row, and that is where the issue is.

Based on your data sample screenshot, the filter condition in each of the subqueries,

WHERE DATE = ... AND HOUR(Time) = ...

can produce more than one row, because there can be many rows having the same DATE and HOUR(Time). Therefore, those subqueries will simply fail to evaluate. You must ensure that each subquery returns one row at most. There might be more than one solution to that but this issue is out of the scope of the current question. Feel free to post a new question if you are struggling to resolve it on your own.

Leave a Reply

Your email address will not be published. Required fields are marked *