The world of SQL dialects is messy and we chose a solution to separate dates and times to avoid some issues with SQL translation. And for 80% of analysis this will be fine.
Currently, the SqlRender package does not provide any translation for hours difference.
But for analyses that need this granularity, this is a future adoption problem.
To evaluate pleural effusion in your CDM based study that uses measurement table (CDM v5) - we need plural protein within 0.5 hours of serum protein. So we happen to hit the remaining 20% world
I would like to propose adding into SqlRender transation of
tsql:datediff(hour,@start,@end) into redshift:??? tsql:datediff(hour,@start,@end) into oracle:(@end - @start) tsql:datediff(hour,@start,@end) into netezza:(@end - @start)
we would also have to handle
And also because things can happen around midnight and we may have to combine the date and time into datetime. (do what the CDM developers try to avoid, but it comes back… )
(or write separate SQL to first compare the date and then the time (plus around midnight logic)… (oh boy…)
I would like to get community opinion on this soft spot of "the temporal world of hours"
(It is most applicable to “EHR-ish CDM sites” rathe than claim-ish sites.)
Most likely in our final study, we will create a temp table in the parametized SQL that will have a datetime column something like
"DATEFROMPARTS(measurement_date, measurement_time) as zzz_event_dt"
(and hope that all our data partners use the same dialect or are good at tweaking datetime SQL code)
I would like propose (mainly to SqlRender developers) to add some “limited support” for hours in the SqlRender - knowing that things can break (something like current RedShift support)