dbplyr and SQL date calculations

[beginnersluke]

I'm using dbplyr to query an SQL database. The important columns are AppointmentDateTime and PatientDOB.

I'm trying to filter the data by the age at appt time.

What I have seems to work fine, but it seems like there may be a better way.

Here is what I have:

mutate(ApptAge = AppointmentDateTime - PatientDOB) %>% 
filter(ApptAge < "1919-01-01") %>% 
collect()

As you can see ApptAge is returned as a date (since 1900-01-01).

How could this be improved?

This also has an issue with leap-years, with the year since 1900 being impacted by those leap years which might be different than the number of leap years between the appointment and PatientDOB.

(My thought is that the code will be a lot more readable if I just pull in a bit extra from SQL, then determine the age using day/month combo then year to determine the age.)

I know there are many wiser than me here, so I just wondered if anyone had insights I could learn from.

Thanks,

Luke

[edgararuiz]

Hi, how about extracting and then operating by year of DOB and appt?

mutate(ApptAge = year(AppointmentDateTime) - year(PatientDOB)) %>%
   filter(ApptAge < {the age in years})

[beginnersluke]

I think I'd need to use DATEPART to get the year. I'm trying to get the syntax right on that. (So far, unsuccessfully.)

EDIT: I got it. I was putting quotes around the first argument, but it works with

mutate(apptYear = DATEPART(yy, AppointmentDateTime)

[beginnersluke]

I think using DATEPART to get the day, month, year, then setting up the logic to determine if the birthday that year had passed + the difference in appt year and birth year is the elegant way to do this.

I'm still curious how I could get R to return the original calculation in seconds (which is what I take it the server actually returns) instead of the date. as_integer() did not work. (Maybe the server does return a date. I'd have to run the SQL itself to see that.)

Thanks

[edgararuiz]

Using year(variable) does not work by default with your database? The SQL ("DATEPART") syntax is translated by dbplyr for several different database backends

[beginnersluke]

Yeah, year(variable) didn't work for me. (I have lubridate loaded, so perhaps it was going to lubridate::year; I'll have to experiment without lubridate loaded.)

[edgararuiz]

Oh ok, what is the database backend that you are interacting with?

[beginnersluke]

It's a proprietary piece of Electronic Health Record software.