postgresqldatetimetimestamp

How to select within last 30 days from date in PostgreSQL?

SQL table has data on taxi drivers, columns are :

id  integer
client_id   integer (Foreign keyed to events.rider_id)
driver_id   integer
city_id integer (Foreign keyed to cities.city_id)
client_rating   integer
driver_rating   integer
predicted_eta   integer
actual_eta  integer
first_completed_date Timestamp ,
status  Enum(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)

How to calculate 90th percentile difference between Actual and Predicted ETA for all completed trips within the last 30 days?

select

  percentile_cont(0.90) within group (order by actual_eta-predicted_eta) as percentile_90_diff

from trips t where status='completed'

and first_completed_date > (first_completed_date - INTERVAL '30 DAY')::DATE

I'm concerned with the last part - first_completed_date > - within last 30 days. Is it correct way to calculate last 30 days from first_completed_date?

Solution

  • You compared the "first_completed_date" to itself, however, need to compare it to the current date minus 30 days

    first_completed_date > NOW() - INTERVAL '30 DAY';