{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"5a508ae1-db32-4684-bc8e-fed4985740fb"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"d2bd480e-090f-4a7b-9049-6549e52b4a70"}},{"type":"paragraph","content":[{"text":"Often one needs to perform queries based on calculated fields of dates or number fields.","type":"text"}]},{"type":"paragraph","content":[{"text":"Examples:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Difference between collection time and date of birth (age at collection)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Difference between collection time and received time (warm ischemia time)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Difference between frozen time and received time","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"All specimens collected \"today\", \"last week\" or \"last month\"","type":"text"}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Demonstration of a Temporal Query: ","type":"text","marks":[{"type":"strong"}]}]},{"type":"embedCard","attrs":{"layout":"align-start","originalHeight":480.0,"width":33.33,"url":"https://youtu.be/VFNAhwMgw7A","originalWidth":853.34}},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Temporal functions","type":"text"}]},{"type":"expand","attrs":{"title":"Click here to view functions..."},"content":[{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"When calculating the difference between two date fields or formatting a date to display in a specific format, there are two parts to consider and include “","type":"text"},{"text":"Parameterization","type":"text","marks":[{"type":"link","attrs":{"href":"https://openspecimen.atlassian.net/wiki/x/B4BQBw"}}]},{"text":"”:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Left-Hand Side:","type":"text","marks":[{"type":"strong"}]},{"text":" This includes the function and fields you wish to evaluate. For example:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Calculate 'Minutes':","type":"text"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"minutes_between(Specimen.customFields.additional_specimen_detail_form.cold_ischemia_end, Specimen.customFields.additional_specimen_detail_form.cold_ischemia_start)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To format a date:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"date_format(Participant.regDate, \"%month_day% %month3% %year4%\")","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Right-Hand Side:","type":"text","marks":[{"type":"strong"}]},{"text":" This represents the condition to check against. Examples include:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"any","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"> 20","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"<= 50","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"18 Jan 2017","type":"text","marks":[{"type":"code"}]},{"text":" (when using date format)","type":"text"}]}]}]}]}]}]},{"type":"table","attrs":{"layout":"full-width","localId":"2dcde425-9c02-402a-b3e6-f952fe88d353"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"Function","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"months_between","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"To find the number of months between two dates. Example: month between registration date and visit date","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"minutes_between","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"To find the number of minutes between two timestamp fields. Example: time difference between frozen and received time","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"years_between","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"To find the number of years between two dates. Example: age at collectionThese functions can be used as below as well","type":"text"},{"text":"a) Days = minutes_between(d1, d2) / (24 * 60)","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]},{"type":"hardBreak"},{"text":"b) Hours = minutes_between(d1, d2) / 60","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]},{"type":"hardBreak"},{"text":"c) Weeks = minutes_between(d1, d2) / (7 * 24 * 60)","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"current_date()","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"To get current date. Example: age as of today","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"round()","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"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. ","type":"text"},{"type":"hardBreak"},{"text":"Usage: round(expression, x)","type":"text"},{"type":"hardBreak"},{"text":"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: ","type":"text"},{"text":"round( years_between( current_date(), Participant.dateOfBirth), 0) any ","type":"text","marks":[{"type":"em"}]},{"text":"will keep 0 digits after decimal.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":" date_range(date, range_type, [interval])","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"Allows to check for data that falls in the particular date range.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"date: can be any form field or arithmetic expression whose result is of date type","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"range_type: possible values are ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_cal_qtr: last calendar quarter","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_cal_qtr: next calendar quarter (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_qtr: last quarter","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_qtr: next quarter (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_cal_month: last calendar month","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_cal_month: next calendar month (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_month: last month","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_month: next month (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_week: last calendar week (new in v4.0)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_week: next calendar week (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"current_week: this week (new in v4.0)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"last_days: last N calendar days (new in v4.0)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"next_days: next N calendar days (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"yesterday: yesterday's calendar date. Special form of last_days where N = 1","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"tomorrow: tomorrow's calendar date. Special form of next_days where N = 1 (new in v6.1)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"today: today's calendar date (new in v4.0)","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"interval: a positive integer. Optional. When not specified, its value is assumed as 1","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Date Range Temporal Filters Illustration: ","type":"text","marks":[{"type":"strong"}]},{"text":"Assuming the present date as 15th March 2017","type":"text"}]},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"nested-table","text":"","parameters":{"cxhtml":"

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 in current week that is from ‘Sunday to Saturday'. If a query is run in the middle of the week, it will give results from 'Sunday to the current day’.	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

","nestedContent":{"type":"doc","content":[{"type":"table","attrs":{"layout":"align-start","width":1800.0,"localId":"cc40a50b-2362-4d33-bd76-0003b8f442eb"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"background":"#f0f1f2","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Expression","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"background":"#f0f1f2","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"background":"#f0f1f2","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Date Range","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Specimen.createdOn, last_cal_qtr, 2)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Specimens created in last 2 calendar quarters","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"1st July 2016 00:00 to 31st December 2016 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Specimen.createdOn, last_qtr, 2)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Specimens created in last 2 quarters","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"1st September 2016 00:00 to 28th February 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Specimen.createdOn, last_cal_month, 3)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Specimens created in last 3 calendar months","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"1st December 2016 00:00 to 28th February 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Specimen.createdOn, last_month, 3)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Specimens created in last 3 months","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"15th December 2016 00:00 to 14th March 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Participant.regDate, today)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Participants registered today","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"15th March 2017 00:00 to 15th March 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Participant.regDate, yesterday)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Participants registered yesterday","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"14th March 2017 00:00 to 14th March 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Participant.regDate, last_days, 10)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Participants registered in last 10 days","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"5th March 2017 00:00 to 14th March 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Participant.regDate, current_week)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Participants registered in current week that is from ‘Sunday to Saturday'. If a query is run in the middle of the week, it will give results from 'Sunday to the current day’.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"12th March 2017 00:00 to 18th March 2017 23:59","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date_range(Participant.regDate, last_week, 2)","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Participants registered in last 2 weeks","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"26th February 2017 00:00 to 11th March 2017 23:59","type":"text"}]}]}]}]}],"version":1},"adf":"{\"type\":\"doc\",\"content\":[{\"type\":\"table\",\"attrs\":{\"layout\":\"align-start\",\"width\":1800.0,\"localId\":\"cc40a50b-2362-4d33-bd76-0003b8f442eb\"},\"content\":[{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"background\":\"#f0f1f2\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Expression\",\"type\":\"text\"}]}]},{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"background\":\"#f0f1f2\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Description\",\"type\":\"text\"}]}]},{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"background\":\"#f0f1f2\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Date Range\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Specimen.createdOn, last_cal_qtr, 2)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Specimens created in last 2 calendar quarters\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"1st July 2016 00:00 to 31st December 2016 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Specimen.createdOn, last_qtr, 2)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Specimens created in last 2 quarters\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"1st September 2016 00:00 to 28th February 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Specimen.createdOn, last_cal_month, 3)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Specimens created in last 3 calendar months\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"1st December 2016 00:00 to 28th February 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Specimen.createdOn, last_month, 3)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Specimens created in last 3 months\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"15th December 2016 00:00 to 14th March 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Participant.regDate, today)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Participants registered today\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"15th March 2017 00:00 to 15th March 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Participant.regDate, yesterday)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Participants registered yesterday\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"14th March 2017 00:00 to 14th March 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Participant.regDate, last_days, 10)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Participants registered in last 10 days\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"5th March 2017 00:00 to 14th March 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Participant.regDate, current_week)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Participants registered in current week that is from ‘Sunday to Saturday'. If a query is run in the middle of the week, it will give results from 'Sunday to the current day’.\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"12th March 2017 00:00 to 18th March 2017 23:59\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"date_range(Participant.regDate, last_week, 2)\",\"type\":\"text\",\"marks\":[{\"type\":\"code\"}]}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Participants registered in last 2 weeks\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"background\":\"#ffffff\",\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"26th February 2017 00:00 to 11th March 2017 23:59\",\"type\":\"text\"}]}]}]}]}],\"version\":1}","reason":"A table in a table cell can't be created or edited in the new editor."}}}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[420.0]},"content":[{"type":"paragraph","content":[{"text":"date_format(date_expr, format)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1350.0]},"content":[{"type":"paragraph","content":[{"text":"Outputs date/time field/expression value in desired format.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"date_expr: Any valid AQL expression that yields date/time type result","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"format: Format of the output string and can be made up of following format specifier tokens.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Values in examples of below table are given w.r.t date/time 18th January 2017 16:45:33","type":"text"}]},{"type":"paragraph","content":[{"text":"date_format(Participant.regDate, \"%month_day% %month3% %year4%\") > \"18 Jan 2017\"","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1037,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1534,"alt":"image-20250515-065623.png","id":"81908a5d-53ea-4cd5-93e5-000ec6dcf063","collection":"contentId-72024115","type":"file","height":647}}]},{"type":"paragraph","content":[{"text":"Only those participants registered after 18 JAN 2017 will be displayed in the desired format. Similarly, you can use the other operators like < , = etc","type":"text"}]},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"nested-table","text":"","parameters":{"cxhtml":"

Specifier	Description	Example
%year4%	Four digit year	2017
%year2%	Two digit year	17
%month2%	Two digit month	01
%month3%	Three character abbreviation of month name	Jan
%month%	Month name string	January
%month_day%	Two digit day of month	18
%hour%	Hour of the day in 24 hour format 00-23	16
%hour12%	Hour of the day in 12 hour format 01-12	04
%minute%	Minute of the hour	45
%second%	Seconds of the minute	33
%meridian%	AM or PM	PM

","nestedContent":{"type":"doc","content":[{"type":"table","attrs":{"layout":"default","localId":"3920ef42-fafd-4ad7-aa1c-7f65404408a7"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Specifier","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%year4%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Four digit year","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"2017","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%year2%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Two digit year","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"17","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%month2%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Two digit month","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"01","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%month3%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Three character abbreviation of month name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Jan","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%month%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Month name string","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"January","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%month_day%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Two digit day of month","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"18","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%hour%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Hour of the day in 24 hour format 00-23","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"16","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%hour12%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Hour of the day in 12 hour format 01-12","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"04","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%minute%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Minute of the hour","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"45","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%second%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Seconds of the minute","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"33","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"%meridian%","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"AM or PM","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"PM","type":"text"}]}]}]}]}],"version":1},"adf":"{\"type\":\"doc\",\"content\":[{\"type\":\"table\",\"attrs\":{\"layout\":\"default\",\"localId\":\"3920ef42-fafd-4ad7-aa1c-7f65404408a7\"},\"content\":[{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Specifier\",\"type\":\"text\"}]}]},{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Description\",\"type\":\"text\"}]}]},{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Example\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%year4%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Four digit year\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"2017\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%year2%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Two digit year\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"17\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%month2%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Two digit month\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"01\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%month3%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Three character abbreviation of month name\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Jan\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%month%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Month name string\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"January\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%month_day%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Two digit day of month\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"18\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%hour%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Hour of the day in 24 hour format 00-23\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"16\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%hour12%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Hour of the day in 12 hour format 01-12\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"04\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%minute%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Minute of the hour\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"45\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%second%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Seconds of the minute\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"33\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"%meridian%\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"AM or PM\",\"type\":\"text\"}]}]},{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"PM\",\"type\":\"text\"}]}]}]}]}],\"version\":1}","reason":"A table in a table cell can't be created or edited in the new editor."}}}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples of temporal queries","type":"text"}]},{"type":"expand","attrs":{"title":"Click here to view examples..."},"content":[{"type":"paragraph","content":[{"text":"Example 1:","type":"text","marks":[{"type":"strong"}]},{"text":" Age as of today ","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Note: ","type":"text","marks":[{"type":"strong"}]},{"text":"If the \"date of birth\" value is blank for the participant, the query results would be blank.","type":"text"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1423,"alt":"image-20250515-070657.png","id":"89ab8b93-22f7-47ee-9dde-49f91a5a12fb","collection":"contentId-72024115","type":"file","height":663}}]},{"type":"paragraph","content":[{"text":"Example 2:","type":"text","marks":[{"type":"strong"}]},{"text":" Age at collection","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Note: ","type":"text","marks":[{"type":"strong"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This expression only works for \"parent (New)\" specimens.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If the \"date of birth\" value is blank for the participant, the query results would be blank.","type":"text"}]}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1533,"alt":"image-20250515-071157.png","id":"9d56c9eb-762e-4a6e-ab8e-83f900b6ab53","collection":"contentId-72024115","type":"file","height":685}}]},{"type":"paragraph","content":[{"text":"Temporal Expression: ","type":"text"},{"text":"\"years_between( Specimen.extensions.SpecimenCollectionEvent.time, Participant.dateOfBirth) any\"","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"Example 3:","type":"text","marks":[{"type":"strong"}]},{"text":" Age at collection","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This expression works for specimens at any level (New, Derived, and Aliquot).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If the \"date of birth\" value is blank for the participant, the query results would be blank.","type":"text"}]}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1536,"alt":"image-20250515-071234.png","id":"7d4c748f-a0e2-46b9-8038-e9bee2d45d01","collection":"contentId-72024115","type":"file","height":690}}]},{"type":"paragraph","content":[{"text":"Temporal Expression: \"","type":"text"},{"text":"years_between( Specimen.spmnCollRecvDetails.collTime, Participant.dateOfBirth) any","type":"text","marks":[{"type":"em"}]},{"text":"\"","type":"text"}]},{"type":"paragraph","content":[{"text":"Example 4:","type":"text","marks":[{"type":"strong"}]},{"text":" Time difference between frozen and received time","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1535,"alt":"image-20250515-071324.png","id":"49803381-ab7b-4981-a6b4-b1b788233044","collection":"contentId-72024115","type":"file","height":689}}]},{"type":"paragraph","content":[{"text":"Example 5: ","type":"text","marks":[{"type":"strong"}]},{"text":"Tissue specimens collected in the last 2 months","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1536,"alt":"image-20250515-071519.png","id":"004b077b-21a7-4e34-a532-44245f57be40","collection":"contentId-72024115","type":"file","height":686}}]},{"type":"paragraph","content":[{"text":"Example 6: ","type":"text","marks":[{"type":"strong"}]},{"text":"Calculating the sample amount","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1536,"alt":"image-20250515-071735.png","id":"78101378-8d9e-41a4-b243-ac281b8c7cf0","collection":"contentId-72024115","type":"file","height":688}}]},{"type":"paragraph","content":[{"text":"Example 7: ","type":"text","marks":[{"type":"strong"}]},{"text":"Count of specimens collected per month","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1552,"alt":"image-20250516-021658.png","id":"adbd2795-645b-46ed-8acf-1a1cfd9fb05e","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"Example 8: ","type":"text","marks":[{"type":"strong"}]},{"text":"Count of participants registered per month.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-022239.png","id":"5892e1e2-053e-49ed-b62c-ae5e12f1f4be","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"Example 9: ","type":"text","marks":[{"type":"strong"}]},{"text":"Count of specimens collected after a particular date","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-022339.png","id":"7da057b5-4ebc-4271-8e2c-443ce2e4aaf9","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"Example 10:","type":"text","marks":[{"type":"strong"}]},{"text":" Age in years (rounded off)","type":"text"}]},{"type":"codeBlock","content":[{"text":"Expression: round( years_between( Participant.regDate, Participant.dateOfBirth), 0) any","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1025,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1025,"alt":"image-20250519-115107.png","id":"a5753f81-e0ca-4fa3-894f-3149afa7fde1","collection":"contentId-72024115","type":"file","height":500}}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Example 11:","type":"text","marks":[{"type":"strong"}]},{"text":" Age in years (rounded off) between 10-20 age","type":"text"}]},{"type":"codeBlock","content":[{"text":"Expression: round( years_between( Participant.regDate, Participant.dateOfBirth), 0) between (10,20)","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1021,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1021,"alt":"image-20250519-115541.png","id":"1e2c190f-1305-4a8a-a8b6-944dcdc393fe","collection":"contentId-72024115","type":"file","height":528}}]},{"type":"paragraph","content":[{"text":"Example 12:","type":"text","marks":[{"type":"strong"}]},{"text":" Number of ","type":"text"},{"text":"participants enrolled and parent samples entered per week.","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-022611.png","id":"a57b6eda-9031-4e44-a6ad-7b111327531a","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"Result view:","type":"text","marks":[{"type":"strong"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-022910.png","id":"1e27a884-4723-41a5-9dc3-a8dc4ee29bb1","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Note: ","type":"text","marks":[{"type":"strong"}]},{"text":"Select the count of participant and primary specimen in '","type":"text"},{"text":"Aggregates","type":"text","marks":[{"type":"textColor","attrs":{"color":"#232323"}}]},{"text":"' column to view the count in the result view.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Example 13:","type":"text","marks":[{"type":"strong"}]},{"text":" Finding percent from 2 numbers in custom form","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-134727.png","id":"11fb08f9-63f0-4a21-b001-121d2325a2e9","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"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. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Example 14:","type":"text","marks":[{"type":"strong"}]},{"text":" Calculating the initial cell value from the volume processed and initial concentration, where the volume processed and initial concentration are specimen custom fields.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1378,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1536,"alt":"image-20250516-134137.png","id":"863f3d09-7778-4f6f-99cb-46f978a0eb4d","collection":"contentId-72024115","type":"file","height":689}}]},{"type":"paragraph","content":[{"text":"In the above expression, ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"aj_specimen_form is the variable name of the custom form.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"initial_processing_volume and initial_cell_volume are the variable names of the volume processed and initial concentration custom fields.","type":"text"}]}]}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Example 15:","type":"text","marks":[{"type":"strong"}]},{"text":" Average of 2 custom fields.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":1407,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1555,"alt":"image-20250516-135438.png","id":"a35b7913-8e79-469d-898e-483c396897d8","collection":"contentId-72024115","type":"file","height":800}}]},{"type":"paragraph","content":[{"text":"In the above expression,","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"'skin_cancer_registration_custom_field_form' is the variable name of the form name.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"‘number_field_1' and 'number_field_2’ are variable names of the custom fields.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The CP (e.g. 'Skin Cancer Study') must be selected, for custom forms and fields. ","type":"text"}]}]}]},{"type":"paragraph"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Parameterized Temporal Filter","type":"text"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Exists: to specify existence condition like age exists","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Any: to specify don't care condition like age any ","type":"text"}]}]}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":55.0},"content":[{"type":"media","attrs":{"width":2560,"id":"9719832e-691d-4496-a528-fe8e5cdaac3e","collection":"contentId-72024115","type":"file","height":1006}}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Calculated URLs","type":"text"}]},{"type":"expand","attrs":{"title":"Expand to know more..."},"content":[{"type":"paragraph","content":[{"text":"From v10.2 onwards, it is possible to configure URLs using temporal expression.","type":"text"}]},{"type":"paragraph","content":[{"text":"For the test server URL of participant overview page '","type":"text"},{"type":"inlineCard","attrs":{"url":"https://test.openspecimen.org/#/cp-view/6636/participants/176857/detail/overview"}},{"text":" ', you need to configure the temporal expression as 'concat(\"","type":"text"},{"type":"inlineCard","attrs":{"url":"https://test.openspecimen.org/#/cp-view/"}},{"text":" \" ,","type":"text"},{"text":"CollectionProtocol.id","type":"text","marks":[{"type":"link","attrs":{"href":"http://CollectionProtocol.id"}}]},{"text":", \"/participants/\" ,","type":"text"},{"text":"Participant.id","type":"text","marks":[{"type":"link","attrs":{"href":"http://Participant.id"}}]},{"text":" , \"/detail/overview\") != \" \"","type":"text"}]},{"type":"paragraph","content":[{"text":"For visit overview page URL ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://test.openspecimen.org/#/cp-view/6636/participants/176857/visits/detail/overview?visitId=130492"}},{"text":" you need to configure the temporal expression as concat(\"","type":"text"},{"type":"inlineCard","attrs":{"url":"https://test.openspecimen.org/#/cp-view/%22"}},{"text":" ,","type":"text"},{"text":"CollectionProtocol.id","type":"text","marks":[{"type":"link","attrs":{"href":"http://CollectionProtocol.id"}}]},{"text":", \"/participants/\" ,","type":"text"},{"text":"Participant.id","type":"text","marks":[{"type":"link","attrs":{"href":"http://Participant.id"}}]},{"text":", \"/visits/detail/overview?visitId=\", ","type":"text"},{"text":"SpecimenCollectionGroup.id","type":"text","marks":[{"type":"link","attrs":{"href":"http://SpecimenCollectionGroup.id"}}]},{"text":") != \" \"","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":50.0},"content":[{"type":"media","attrs":{"width":1450,"id":"44863b02-ca4a-40ad-bb53-b188c0bb9159","collection":"contentId-72024115","type":"file","height":632}}]},{"type":"paragraph","content":[{"text":"Below is the list of fields that are available to be used in the query","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"e9564605-f959-45b6-9080-3c46bb6afaf4"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"Syntax","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Collection Protocol Identifier","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"CollectionProtocol.id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Participant Registration Identifier","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"participant.id ","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Participant Identifier","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"Participant.participantId","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Visit Identifier","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"SpecimenCollectionGroup.id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[793.0]},"content":[{"type":"paragraph","content":[{"text":"Specimen Identifier","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[977.0]},"content":[{"type":"paragraph","content":[{"text":"Specimen.id","type":"text"}]}]}]}]}]},{"type":"paragraph"}],"version":1}