How to produce generic time interval views of PostgreSQL events table?

165 views Asked by At

I have a PostgreSQL query which generates a view that buckets only the first 30 days of data into the view.

CREATE view vet_visits_over_first_30_days AS
(SELECT veterinarian_session.animal_id AS "animal", veterinarian_account.enrollment_date AS "Enrolled", veterinarian_sessionactivity.created_date AS "Admit Date", veterinarian_sessionactivity.detail AS "Reason" 
FROM veterinarian_account, veterinarian_sessionactivity, veterinarian_session 
WHERE veterinarian_sessionactivity.created_date BETWEEN veterinarian_account.enrollment_date AND veterinarian_account.enrollment_date + '30 days'::INTERVAL AND veterinarian_sessionactivity.activity_sub_id=52 AND veterinarian_sessionactivity.session_id = veterinarian_session.id AND veterinarian_session.animal_id=veterinarian_account.id 
ORDER BY veterinarian_session.animal_id);

which will then let me do:

select * from vet_visits_over_first_30_days;

and get back only the visits in the first 30 days.

However, I need to do this generically over a large number of tables which have the same date fields over different time buckets such as 30, 60, 90, 180 days.

I think I could achieve this using SQL metaprogramming and views, but is there a better way using PostgreSQL?

0

There are 0 answers