0

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
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.

  • It gives me: ERROR: syntax error at or near "over" LINE 2: lag(mt.insert_time) as next_time over(w) – VirtApp Oct 5 '20 at 17:13
  • @VirtApp: oops, please see the edit with a fix. Note that I use ->>'kw' to extract a text rather than a json (with ->'kw') otherwise you can't cast to int. I don't know how the query in the question gets aways with that. – Daniel Vérité Oct 5 '20 at 17:45
  • Thanks Daniel, so, if i'm not wrong, you suggest me to use the lag functions to put the current and next measurements on the same line (specifying the kw > 1 in the where) and then get the duration in minutes? – VirtApp Oct 5 '20 at 19:40
  • 1
    @VirtApp: yes. Also maybe you don't need next_value. I think the gist of your question is how to get next_time. – Daniel Vérité Oct 6 '20 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.