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

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!

CodePudding user response：

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)