[SOLVED] CnosDB How to Calculate Derivatives through a Time Window

CnosDB How to Calculate Derivatives through a Time Window

I would like to use the following SQL to take the derivative of the data, but EXTRACT does not work as expected, is there an alternative way?

SELECT
    time,
    value,
    (value - LAG(value) OVER (ORDER BY time)) / EXTRACT(EPOCH FROM (time - LAG(time) OVER (ORDER BY time))) AS derivative
FROM
  (VALUES 
    ('2024-01-01 00:00:00'::TIMESTAMP, 10.0),
    ('2024-01-01 00:01:00'::TIMESTAMP, 12.0),
    ('2024-01-01 00:02:00'::TIMESTAMP, 15.0),
    ('2024-01-01 00:03:00'::TIMESTAMP, 18.0),
    ('2024-01-01 00:04:00'::TIMESTAMP, 20.0)
  ) AS data(time, value);

Solution

I have found the correct method of taking the derivative based on time, and it seems to have no problem

SELECT
    time,
    (value - LAG(value) OVER (ORDER BY time)) / (CAST(time AS BIGINT) - CAST(LAG(time) OVER (ORDER BY time) AS BIGINT)) AS derivative
FROM
  (VALUES
    ('2024-01-01 00:00:00'::TIMESTAMP, 10.0),
    ('2024-01-01 00:01:00'::TIMESTAMP, 12.0),
    ('2024-01-01 00:02:00'::TIMESTAMP, 15.0),
    ('2024-01-01 00:03:00'::TIMESTAMP, 18.0),
    ('2024-01-01 00:04:00'::TIMESTAMP, 20.0)
  ) AS data(time, value) ORDER BY time;

The final result is:

+---------------------+------------------------+
| time                | derivative             |
+---------------------+------------------------+
| 2024-01-01T00:00:00 |                        |
| 2024-01-01T00:01:00 | 3.3333333333333335e-11 |
| 2024-01-01T00:02:00 | 5.0e-11                |
| 2024-01-01T00:03:00 | 5.0e-11                |
| 2024-01-01T00:04:00 | 3.3333333333333335e-11 |
+---------------------+------------------------+