![]() ![]() I have a lot of issues with this query: i need to repeat this query (on grafana) for all my devices (that are around 15) and it takes a lot of time: producing very high CPU and Memory usage, for completeness, here it's the execution plan with EXPLAIN ANALYZE VERBOSE TRUNC(sum("total_seconds")::numeric/3600, 2) AS "Hours" -Hours ![]() SELECT -Calculates total sum of hours and days over temp cte table WHERE smart_device_id = 8 - Filter SectionĪND date_part('month', mt.insert_time) = 10ĪND date_part('year', mt.insert_time) = 2020 (DATE_PART('day', lag(mt.insert_time) OVER w - mt.insert_time) * 24 + -Calculates the delta between the two measurements in secondsĭATE_PART('hour', lag(mt.insert_time) OVER w - mt.insert_time)* 60 +ĭATE_PART('minute', lag(mt.insert_time) OVER w - mt.insert_time) * 60 +ĭATE_PART('second', lag(mt.insert_time) OVER w - mt.insert_time)) as total_seconds | 3003711, 09:55:54.271738+00,"",12 |īasically, with the following query, I'm able to specify a month and a year and then calculate the total seconds of kW production, after that, I'll calculate the total hours and days over the cte aggregated table, here is the complete query: with cte_temp as ( The schema of the table is the following one: +-+ As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then extract the minutes of production hours of my solar panel system. ![]()
0 Comments
Leave a Reply. |