'need the dv/dt for the below table

How to get the dv/dt of the below table in psql Concept is linear regression - but to determine the slope is the what I am facing issue with.

voltage ||   time
0          2022-04-25 12:40:32.071 
0          2022-04-25 12:41:32.071
0          2022-04-25 12:42:32.071
9          2022-04-25 12:43:32.071
10         2022-04-25 12:44:32.071
11         2022-04-25 12:45:32.071
0          2022-04-25 12:46:32.071
0          2022-04-25 12:47:32.071
0          2022-04-25 12:48:32.071
0          2022-04-25 12:49:32.071

table name is test_voltage

expected outcome - 
voltage   ||   time
0           2022-04-25 12:40:32.071  || idle
0           2022-04-25 12:41:32.071  || idle 
0           2022-04-25 12:42:32.071  || idle
9           2022-04-25 12:43:32.071  || charging
10          2022-04-25 12:44:32.071  || charging
11          2022-04-25 12:45:32.071  || charging
0           2022-04-25 12:46:32.071  || idle
0           2022-04-25 12:47:32.071  || idle
0           2022-04-25 12:48:32.071  || idle
0           2022-04-25 12:49:32.071  || idle
sqlpostgresqlplpgsql


Solution 1:[1]

column information time is timex y is voltage table_name is test_slope case name is binaries slope query is named as ydiff

select time, y,
case when ((LAG(y) over (order by time asc) - y)/( LAG(date_part('epoch', time)) 
 OVER (ORDER BY time) - date_part('epoch', time)) ) > 0 then 'charging'
else 'idle'
end binaries
from 
  table_name 
  order by time asc;

Picture

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user17476256

Related Questions