Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 9.6.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE trajectories_splitted
(
    user_id integer,
    session_id bigint NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    alt double precision,
  
    CONSTRAINT trajectories_splitted_pkey PRIMARY KEY (session_id, "timestamp")
)
INSERT INTO trajectories_splitted (user_id, session_id,timestamp,lat,lon,alt)
VALUES   (1,20081023025304,'2008-10-23 02:53:04+01',39.984702,116.318417,492),
(1,20081023025304,'2008-10-23 02:53:10+01',39.984683,116.31845,492),
       (1,20081023025304,'2008-10-23 02:53:15+01',39.984686,116.318417,492),
       (1,20081023025304,'2008-10-23 02:53:20+01',39.984688,116.318385,492),
       (1,20081023025304,'2008-10-23 02:53:25+01',39.984655,116.318263,492),
       (1,20081023025304,'2008-10-23 02:53:30+01',39.984611,116.318026,493),
       (1,20081023025305,'2008-10-23 04:08:07+01',39.995777,116.286798,716),          
       (1,20081023025305,'2008-10-23 04:08:12+01',39.996832,116.285446,276 ),
       (1,20081023025305,'2008-10-23 04:08:42+01',39.984397,116.299292,931),
       (1,20081023025305,'2008-10-23 04:08:47+01',39.984426,116.299329,959),
       (1,20081023025305,'2008-10-23 04:08:52+01',39.984499,116.299413,983),
  (1,20081023025305,'2008-10-23 04:08:57+01',39.984424,116.299467,990),
       (1,20081023025305,'2008-10-23 04:09:02+01',39.98441,116.299477,940),
       (1,20081023025305,'2008-10-23 04:09:07+01',39.984421,116.299569,928),
       (1,20081023025305,'2008-10-23 04:09:12+01',39.984518,116.29953,902),
       (1,20081023025305,'2008-10-23 04:09:17+01',39.984488,116.299645,897)
16 rows affected

select * from trajectories_splitted


user_id
session_id
timestamp
lat
lon
alt


1
20081023025304
2008-10-23 02:53:04+01
39.984702
116.318417
492

1
20081023025304
2008-10-23 02:53:10+01
39.984683
116.31845
492

1
20081023025304
2008-10-23 02:53:15+01
39.984686
116.318417
492

1
20081023025304
2008-10-23 02:53:20+01
39.984688
116.318385
492

1
20081023025304
2008-10-23 02:53:25+01
39.984655
116.318263
492

1
20081023025304
2008-10-23 02:53:30+01
39.984611
116.318026
493

1
20081023025305
2008-10-23 04:08:07+01
39.995777
116.286798
716

1
20081023025305
2008-10-23 04:08:12+01
39.996832
116.285446
276

1
20081023025305
2008-10-23 04:08:42+01
39.984397
116.299292
931

1
20081023025305
2008-10-23 04:08:47+01
39.984426
116.299329
959

1
20081023025305
2008-10-23 04:08:52+01
39.984499
116.299413
983

1
20081023025305
2008-10-23 04:08:57+01
39.984424
116.299467
990

1
20081023025305
2008-10-23 04:09:02+01
39.98441
116.299477
940

1
20081023025305
2008-10-23 04:09:07+01
39.984421
116.299569
928

1
20081023025305
2008-10-23 04:09:12+01
39.984518
116.29953
902

1
20081023025305
2008-10-23 04:09:17+01
39.984488
116.299645
897


select session_id,
       count(*) / nullif((max(timestamp) - min(timestamp))::int, 0) as sampling_rate
from trajectories_splitted
group by session_id;
ERROR:  cannot cast type interval to integer
LINE 2: ...ount(*) / nullif((max(timestamp) - min(timestamp))::int, 0) ...
                                                             ^


select session_id,
       (count(*)-1) / nullif((max(timestamp) - min(timestamp)), 0) as sampling_rate
from trajectories_splitted
group by session_id
ERROR:  operator does not exist: interval = integer
LINE 2:        (count(*)-1) / nullif((max(timestamp) - min(timestamp...
                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

user_id	session_id	timestamp	lat	lon	alt
1	20081023025304	2008-10-23 02:53:04+01	39.984702	116.318417	492
1	20081023025304	2008-10-23 02:53:10+01	39.984683	116.31845	492
1	20081023025304	2008-10-23 02:53:15+01	39.984686	116.318417	492
1	20081023025304	2008-10-23 02:53:20+01	39.984688	116.318385	492
1	20081023025304	2008-10-23 02:53:25+01	39.984655	116.318263	492
1	20081023025304	2008-10-23 02:53:30+01	39.984611	116.318026	493
1	20081023025305	2008-10-23 04:08:07+01	39.995777	116.286798	716
1	20081023025305	2008-10-23 04:08:12+01	39.996832	116.285446	276
1	20081023025305	2008-10-23 04:08:42+01	39.984397	116.299292	931
1	20081023025305	2008-10-23 04:08:47+01	39.984426	116.299329	959
1	20081023025305	2008-10-23 04:08:52+01	39.984499	116.299413	983
1	20081023025305	2008-10-23 04:08:57+01	39.984424	116.299467	990
1	20081023025305	2008-10-23 04:09:02+01	39.98441	116.299477	940
1	20081023025305	2008-10-23 04:09:07+01	39.984421	116.299569	928
1	20081023025305	2008-10-23 04:09:12+01	39.984518	116.29953	902
1	20081023025305	2008-10-23 04:09:17+01	39.984488	116.299645	897