How to return rows with dates older than X years from today?

ppp147 0
Viewed 39

I'm writing a query in Postgres to return the avg. length of hospital stay for all patients older than 65 years ago. p.date_of_birth contains dates, like '2020-04-20'. I came up with the following WHERE clause:

SELECT
    AVG(surgical_discharge_date - surgical_admission_date) AS avg_stay
FROM
    hosp.surgical_encounters as e
LEFT JOIN hosp.patients as p
    ON e.master_patient_id = p.master_patient_id
WHERE
    CURRENT_DATE - p.date_of_birth > 23741.25

it returns a value that seems realistic although i'm not sure if it's correct. Does the query return indeed 65+-year-olds? (65 years -> 23741.25 days). Is there a better way to put such conditions, without the need to break it down into days? Thanks!

date sql postgresql
1 Answers

You can use the INTERVAL

select current_date - interval '1 year';

Which today 2022-09-06 returns

      ?column?
---------------------
 2021-09-06 00:00:00
(1 row)
Ftisiot0
Related