Count hours when a specific value is higher than a treshold

All we need is an easy explanation of the problem, so here it is.

i have this JSON that is contained in "data" column, the table also have: insert time, pk and some fk as reported below:

PK  INSERT_TIME                     DATA   SENSOR
1   2020-01-04 15:39:44.19364+00    {}       7

The table is populated with an external application that, about every minute, pushes new data, this is the JSON that i have typically in the table:

{
   "dev":11,
   "kw":43.0,
   "lux":200.2,
   "temp":20.3,
}

My goal is to build a query that, having all rows in the table is able to count how many hours in total and hours per day the device was up and running and producing kW, so, when the kW value is greater than zero (those measurements comes from a solar inverter system)

This is the query that i’ve built, actually i’m able to select only the rows that have the kw value greater than 1 and group them by month, but i’m not able to figure out how to calculate the production hours:

SELECT mt.insert_time, 
       date_part('month', mt.insert_time) as mon
FROM mytable mt 
WHERE sensor = 7 
  AND date_part('month', mt.insert_time) = 5 
  AND (mt.data->'kw')::int > 1

What i’m expecting:

I need to get the exact amount of working hour of my solar panel production system, i’m assuming that the solar panel "works" only when the kW value is greater than zero.

I want to see, day by day, and month by month the working hours of my panel.

Here guys i’m reporting an example of my data rows

+-----------------------------------------------------------------------------------------------------+
|                                     pk,insert_time,data,sensor                                      |
+-----------------------------------------------------------------------------------------------------+
| 3003711,2020-10-03 09:55:54.271738+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.8, ""lux"": 2.0}",12   |
| 3003692,2020-10-03 09:54:54.289131+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.6, ""lux"": 2.0}",12   |
| 3003681,2020-10-03 09:53:54.287502+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003670,2020-10-03 09:52:54.284262+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003659,2020-10-03 09:51:56.382746+00,"{""dev"": 12, ""kw"": 12, ""temp"": 20.5, ""lux"": 2.0}",12  |
| 3003648,2020-10-03 09:50:54.279558+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003637,2020-10-03 09:49:56.377988+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
+-----------------------------------------------------------------------------------------------------+

The final result may produce a query that (starting from a specific day of month, or specific month, or specific year) will give me the number of minutes and hours that the system was powered up and producing kW.

Like, for example:

  • Selecting the date of 10/08/2020 i need to get the total production minutes and respective hours count
  • Selecting the month of july (7) i need to get the total production minutes and respective hours count
  • Selecting the year (2020) i need to get the total production minutes and respective hours count

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Q: This is the query that i’ve built, actually i’m able to select only the rows that have the kw value greater than 1 and group them by
month, but i’m not able to figure out how to calculate the production
hours:

You need to get each measurement and the next measurement into a single row. This is typically what the lag window function does:

SELECT mt.insert_time,
       lag(mt.insert_time) OVER w AS next_time 
       (mt.data->>'kw')::int AS curr_value,
       ((lag(mt.data) OVER w)->>'kw')::int AS next_value
FROM mytable mt 
WHERE sensor = 7 
WINDOW w AS (order by pk)  -- or order by insert_time if you prefer

Once both the current and next measurements are in the same row, you have the duration between them (=next_time - insert_time) and can sum and filter out rows when the kw value went from above 1 to below 1 or the opposite, or were both below 1.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply