Calculated fields (Temporal Queries)

Often one needs to perform queries based on calculated fields of dates or number fields.

Examples:

  1. Difference between collection time and date of birth (age at collection)
  2. Difference between collection time and received time (warm ischemia time)
  3. Difference between frozen time and received time
  4. All specimens collected "today", "last week" or "last month"

This page describes the way to do it and different options available. For video training session, please refer to Wiki page Temporal Query (Video)

Creating a new temporal query


Info:

Attribute names, functions, and operators are auto-completed once you start to type in the temporal expression window.

Temporal functions

FunctionDescription
months_between

To find number of months between two dates. Example: month between registration date and visit date

minutes_betweenTo 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)
b) Hours = minutes_between(d1, d2) / 60
c) Weeks = minutes_between(d1, d2) / (7 * 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.
Usage: round(expression, x)
Here, the expression is the integer value result that is to be rounded. 'x' is the number of decimal places to be rounded up to or the number of digits after the decimal point that the expression should be rounded to.

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: can be any form field or arithmetic expression whose result is of date type
  • range_type: possible values are 
    • last_cal_qtr: last calendar quarter
    • next_cal_qtr: next calendar quarter (new in v6.1)
    • last_qtrlast quarter
    • next_qtr: next quarter (new in v6.1)
    • last_cal_month: last calendar month
    • next_cal_month: next calendar month (new in v6.1)
    • last_month: last month
    • next_month: next month (new in v6.1)
    • last_week: last calendar week (new in v4.0)
    • next_week: next calendar week (new in v6.1)
    • current_week: this week (new in v4.0)
    • last_days: last N calendar days (new in v4.0)
    • next_days: next N calendar days (new in v6.1)
    • yesterday: yesterday's calendar date. Special form of last_days where N = 1
    • tomorrow: tomorrow's calendar date. Special form of next_days where N = 1 (new in v6.1)
    • today: today's calendar date (new in v4.0)
  • interval: a positive integer. Optional. When not specified, its value is assumed as 1

date_format(date_expr, format)

Outputs date/time field/expression value in desired format.

  • date_expr: Any valid AQL expression that yields date/time type result

  • format: Format of the output string and can be made up of following format specifier tokens.

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

SpecifierDescriptionExample
%year4%Four digit year2017
%year2%Two digit year17
%month2%Two digit month01
%month3%Three character abbreviation of month nameJan
%month%Month name stringJanuary
%month_day%Two digit day of month18
%hour%Hour of the day in 24 hour format 00-2316
%hour12%Hour of the day in 12 hour format 01-1204
%minute%Minute of the hour45
%second%Seconds of the minute33
%meridian%AM or PMPM


Example 1: Age as of today 

Note:

If the "date of birth" value is blank for the participant, the query results would be blank.

Example 2: Age at collection

Note:

  1. This expression only works for "parent (New)" specimens.
  2. 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"

Example 3: Age at collection

Note:

  1. This expression works for specimens at any level (New, Derived, and Aliquot).
  2. If the "date of birth" value is blank for the participant, the query results would be blank.


Temporal Expression: "years_between( Specimen.spmnCollRecvDetails.collTime, Participant.dateOfBirth) any"

Example 4: Time difference between frozen and received time



Example 5: Tissue specimens collected in the last 2 months


Example 6: Calculating the sample amount



Example 7: Count of specimens collected per month


Example 8: Count of specimens collected after a particular date


Example 9: Age in years (rounded off)



Example 10: Number of participants enrolled and parent samples entered per week.


Result view:

Note:

Select the count of participant and primary specimen in 'Aggregates' column to view the count in the result view.

Example 11: Finding percent from 2 numbers in custom form

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. 

Parameterized Temporal Filter

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:

  • Exists: to specify existence condition like age exists
  • Any: to specify don't care condition like age any 

Date Range Temporal Filters Illustration

Assuming the present date as 15th March 2017


ExpressionDescriptionDate 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 quarters1st September 2016 00:00 to 28th February 2017 23:59
date_range(Specimen.createdOn, last_cal_month, 3)Specimens created in last 3 calendar months1st December 2016 00:00 to 28th February 2017 23:59
date_range(Specimen.createdOn, last_month, 3)Specimens created in last 3 months15th December 2016 00:00 to 14th March 2017 23:59
date_range(Participant.regDate, today)Participants registered today15th March 2017 00:00 to 15th March 2017 23:59
date_range(Participant.regDate, yesterday)Participants registered yesterday14th March 2017 00:00 to 14th March 2017 23:59
date_range(Participant.regDate, last_days, 10)Participants registered in last 10 days5th March 2017 00:00 to 14th March 2017 23:59
date_range(Participant.regDate, current_week)Participants registered this week12th March 2017 00:00 to 18th March 2017 23:59
date_range(Participant.regDate, last_week, 2)Participants registered in last 2 weeks26th February 2017 00:00 to 11th March 2017 23:59