Postgres - query time series pattern

Question

I have a point pattern in Postgres where the pickup and dropoff times and locations of each taxi trip are recorded for several months. I need to count the number of taxi trips for a temporal window (e.g. between 00:00 and 03:00) for a given interval (e.g. between 5 March and 27 March). Therefore, for the given example, I need to calculate the total taxi trips occurred on 5 and 6 and 7, ..., and 27 March between 00:00 and 03:00.

The only function I found is 'date_trunc', but I don't really think that it is suitable, because the window size is already fixed.

Please, read How to ask a good question

klin
– klin

2018-03-27 20:51:27 +00:00
Commented Mar 27, 2018 at 20:51 — klin
– klin, Commented Mar 27, 2018 at 20:51

Chris Curvey · Accepted Answer · 2018-03-27 21:35:45Z

drop table if exists taxi_trips;

create table taxi_trips
( pickup timestamp
, dropoff timestamp null
, notes varchar(100)
);

insert into taxi_trips (pickup,  dropoff, notes) values ('2/28/2018 07:15', '2/28/2018 7:35', 'not found, date too early');
insert into taxi_trips (pickup,  dropoff, notes) values ('3/5/2018 01:15', '3/5/2018 1:35', 'found');
insert into taxi_trips (pickup,  dropoff, notes) values ('3/5/2018 06:15', '3/5/2018 6:35', 'not found, outside time window');
insert into taxi_trips (pickup,  dropoff, notes) values ('3/6/2018 01:15', '3/6/2018 1:35', 'found');
insert into taxi_trips (pickup,  dropoff, notes) values ('3/6/2018 06:15', '3/6/2018 6:35', 'not found, outside time window');
insert into taxi_trips (pickup,  dropoff, notes) values ('4/1/2018 07:15', '4/1/2018 7:35', 'not found, date too late');

select count(*)
from taxi_trips
where pickup between '3/5/2018' and '3/28/2018'
and extract (hour from pickup) between 0 and 3

Collectives™ on Stack Overflow

Postgres - query time series pattern

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related