Often one needs to perform queries based on calculated fields of dates or number fields.
Examples:
This page describes the way to do it and different options available. For video training session, please refer to Wiki page Temporal Query (Video)
Attribute names, functions, and operators are auto-completed once you start to type in the temporal expression window. |
Function | Description | ||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
months_between | To find number of months between two dates. Example: month between registration date and visit date | ||||||||||||||||||||||||||||||||||||
minutes_between | To find number of minutes between two timestamp fields. Example: time difference between frozen and received time | ||||||||||||||||||||||||||||||||||||
years_between | To find number of years between two dates. Example: age at collection These functions can be used as below as well a) Days = minutes_between(d1, d2) / (24 * 60) | ||||||||||||||||||||||||||||||||||||
current_date() | To get current date. Example: age as of today | ||||||||||||||||||||||||||||||||||||
round() | To round up calculations. In cases where integer values are expected, for example, temporal expressions related to dates, round() function ensures that stable integer results are obtained. Example: round( years_between( current_date(), Participant.dateOfBirth), 0) any will keep 0 digits after decimal. | ||||||||||||||||||||||||||||||||||||
date_range(date, range_type, [interval]) | Allows to check for data that falls in the particular date range.
| ||||||||||||||||||||||||||||||||||||
date_format(date_expr, format) | Outputs date/time field/expression value in desired format.
Values in examples of below table are given w.r.t date/time 18th January 2017 16:45:33 date_format(Participant.regDate, "%month_day% %month3% %year4%") any => 18 Jan 2017
|
If the "date of birth" value is blank for the participant, the query results would be blank. |
|
Temporal Expression: "years_between( Specimen.extensions.SpecimenCollectionEvent.time, Participant.dateOfBirth) any"
|
Temporal Expression: "years_between( Specimen.spmnCollRecvDetails.collTime, Participant.dateOfBirth) any"
Select the count of participant and primary specimen in 'Aggregates' column to view the count in the result view. |
In the above example, number1 and number2 are two numeric fields defined in a form attached at 'Registration Forms' level. There is no % function in temporal queries, but the formula to find percent can directly used to calculate the same.
Temporal filters can be parameterized so that users can enter a range to dynamically filter the results. Below operators can be used in the temporal expression to make it parameterized:
Assuming the present date as 15th March 2017
Expression | Description | Date Range |
---|---|---|
date_range(Specimen.createdOn, last_cal_qtr, 2) | Specimens created in last 2 calendar quarters | 1st July 2016 00:00 to 31st December 2016 23:59 |
date_range(Specimen.createdOn, last_qtr, 2) | Specimens created in last 2 quarters | 1st September 2016 00:00 to 28th February 2017 23:59 |
date_range(Specimen.createdOn, last_cal_month, 3) | Specimens created in last 3 calendar months | 1st December 2016 00:00 to 28th February 2017 23:59 |
date_range(Specimen.createdOn, last_month, 3) | Specimens created in last 3 months | 15th December 2016 00:00 to 14th March 2017 23:59 |
date_range(Participant.regDate, today) | Participants registered today | 15th March 2017 00:00 to 15th March 2017 23:59 |
date_range(Participant.regDate, yesterday) | Participants registered yesterday | 14th March 2017 00:00 to 14th March 2017 23:59 |
date_range(Participant.regDate, last_days, 10) | Participants registered in last 10 days | 5th March 2017 00:00 to 14th March 2017 23:59 |
date_range(Participant.regDate, current_week) | Participants registered this week | 12th March 2017 00:00 to 18th March 2017 23:59 |
date_range(Participant.regDate, last_week, 2) | Participants registered in last 2 weeks | 26th February 2017 00:00 to 11th March 2017 23:59 |