Calculating speed at various points along a route for each device

Question

I want to calculate speed at every point for each device_id, such that there is a new column of "speed" is attached to the current dataset

My dataset is like

imei(device_id)         point_geom(SRID 4326)           time_created

Here is my code so far:

select st_distance((points_geom::geography),lag(points_geom::geography) over (partition by imei order by time_created))
 / (time_created - lag(time_created) over(partition by imei order by time_created))  as speed
from dataset 

I am receiving this error:

SQL Error [42883]: ERROR: operator does not exist: double precision / interval

Answer 1

Arithmetic on two TIMESTAMP types return an INTERVAL type - use their EPOCH second representations instead:

( EXTRACT(EPOCH FROM time_created) - EXTRACT(EPOCH FROM LAG(time_created) OVER(PARTITION BY imei ORDER BY time_created))) )::FLOAT8

---

Note:

You can EXTRACT(EPOCH FROM <INTERVAL>) directly, however the internal INTERVAL transformation function may introduce integer rounding inaccuracies.