Clinical Quality Language Release 1 (Development Build)
This page is part of the Clinical Quality Language Specification (v1.5.0: Normative Ballot 1). The current version which supercedes this version is 1.5.1. For a full list of available versions, see the Directory of published versions 
| Clinical Decision Support Work Group | Maturity Level: N/A | Standards Status: Informative |
To determine the length of time between two dates, CQL provides two different approaches, duration, the number of whole periods between two dates, and difference, the number of period boundaries crossed between two dates.
The first approach, calculating the duration, determines the number of whole periods that occur between the two dates. Conceptually, the calculation is performed by considering the two dates on a timeline, and counting the number of whole periods that fit on that timeline between the two dates. For example:
Date 1: 2012-03-10
Date 2: 2013-03-10
Duration In Years: years between Date1 and Date2
The Duration In Years expression gives one year, because an entire year has passed between the two dates. Note that time is considered for the purposes of calculating the number of years:
DateTime 1: 2012-03-10 10:20:00
DateTime 2: 2013-03-10 09:20:00
Duration in Years: years between DateTime1 and DateTime2
This expression gives zero years, because the year has not passed until 10:20:00 on the day in the following year. To calculate the number of years, ignoring the time, extract the date from the datetime value:
DateTime 1: 2012-03-10 10:20:00
DateTime 2: 2013-03-10 09:20:00
Duration In Years: years between (date from DateTime1) and (date from DateTime2)
The second approach, calculating the difference, determines the number of boundaries crossed between two dates. To illustrate the difference, consider the following example:
Date 1: 2012-12-31
Date 2: 2013-01-01
Duration In Years: years between Date1 and Date2
Difference In Years: difference in years between Date1 and Date2
The Duration In Years expression returns zero because a full year has not passed between the two dates. However, the Difference In Years expression returns 1 because one year boundary was crossed between the two dates.
In CQL, a year is defined as the duration of any time interval which starts at a certain time of day at a certain calendar date of the calendar year and ends at:
The same time of day on the same calendar date of the next calendar year, if it exists
The same time of day on the immediately following calendar date of the next calendar year, if the same calendar date of the next calendar year does not exist.
Note: When in the next calendar year the same calendar date does not exist, the ISO states that the ending calendar day has to be agreed upon. The above convention is used in CQL as a resolution to this issue.
When month (date 2) < month (date 1):
Duration (years) = year (date 2) - year (date 1) - 1
Example 1:
Date 1: 2012-03-10 22:05:09
Date 2: 2013-02-18 19:10:03
Duration = year (date 2) - year (date 1) - 1 = 2013 - 2012 - 1 = 0 years
When month (date 2) = month (date 1) and day (date 2) >= day (date 1)
Duration (years) = year (date 2) - year (date 1)
Example 2.a: day (date 1) = day (date 2)
Date 1: 2012-03-10 22:05:09
Date 2: 2013-03-10 22:05:09
Duration = year (date 2) - year (date 1) = 2013 - 2012 = 1 year
Note: Time of day is important in this calculation. If the time of day of Date 2 were less than the time of day for Date 1, the duration of the time interval would be 0 years according to the definition.
Example 2.b: day (date 2) > day (date 1)
Date 1: 2012-03-10 22:05:09
Date 2: 2013-03-20 04:01:30
Duration = year (date 2) - year (date 1) = 2013 - 2012 = 1 year
When month (date 2) = month (date 1) and day (date 2) < day (date 1)
Duration (years) = year (date 2) - year (date 1) - 1
Example 3.a:
Date 1: 2012-02-29
Date 2: 2014-02-28
Duration = year (date 2) - year (date 1) - 1 = 2014 - 2012 - 1 = 1 year
When month (date 2) > month (date 1)
Duration (years) = year (date 2) - year (date 1)
Example 4.a:
Date 1: 2012-03-10 11:16:02
Date 2: 2013-08-15 21:34:16
Duration = year (date 2) - year (date 1) = 2013 - 2012 - 1 year
Example 4.b:
Date 1: 2012-02-29 10:18:56
Date 2: 2014-03-01 19:02:34
Duration = year (date 2) - year (date 1) = 2014 - 2012 = 2 years
Note: Because there is no February 29 in 2014, the number of years can only change when the date reaches March 1, the first date in 2014 that surpasses the month and day of date 1 (Feburary 29).
When month (date 1) < month (date 2)
Duration (years) = year (date 2) - year (date 1) + 1
Example 5:
Date 1: 2012-02-20 10:20:56
Date 2: 2011-03-19 19:16:02
Duration = year (date 2) - year (date 1) + 1 = 2011 - 2012 + 1 = 0 years
When month (date 2) = month (date 1) and day (date 2) <= day (date 1)
Duration (years) = year (date 2) - year (date 1)
Example 6.a: day (date 1) = day (date 2)
Date 1: 2013-05-11 20:06:10
Date 2: 2012-05-11 20:06:10
Duration = year (date 2) - year (date 1) = 2012 - 2013 = -1 year
Example 6.b: day (date 1) > day (date 2)
Date 1: 2013-05-15 10:19:55
Date 2: 2012-05-10 12:20:25
Duration = year (date 2) - year (date 1) = 2012 - 2013 = -1 year
When month (date 2) = month (date 1) and day (date 2) > day (date 1)
Duration (years) = year (date 2) - year (date 1) + 1
Example 7:
Date 1: 2013-03-20 05:01:30
Date 2: 2011-03-30 06:45:50
Duration = year (date 2) - year (date 1) + 1 = 2011 - 2013 + 1 = -1 year
When month (date 2) < month (date 1)
Duration (years) = year (date 2) - year (date 1)
Example 8.a:
Date 1: 2014-08-12 09:10:15
Date 2: 2013-05-14 01:17:10
Duration = year (date 2) - year (date 1) = 2013 - 2014 = -1 year
Example 8.b:
Date 1: 2015-11-05 05:50:45
Date 2: 2013-03-29 11:01:05
Duration = year (date 2) - year (date 1) = 2013 - 2015 = -2 years
A month in CQL is defined as the duration of any time interval which starts at a certain time of day at a certain calendar day of the calendar month and ends at:
The same time of day at the same calendar day of the ending calendar month, if it exists
The same time of day at the immediately following calendar date of the ending calendar month, if the same calendar date of the ending month in the ending year does not exist.
Notes: When in the next calendar year the same calendar date does not exist, the ISO states that the ending calendar day has to be agreed upon. The above convention is used in CQL as a resolution to this issue.
When day (date 2) >= day (date 1)
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1))
Example 1.a:
Date 1: 2012-03-01 14:05:45
Date 2: 2012-03-31 23:01:49
Duration = (year (date 2) - year (date 1)) * 12 + (month (date 2) - (month (date 1))
= (2012 - 2012) * 12 + (3 - 3) = 0 months
Example 1.b:
Date 1: 2012-03-10 22:05:09
Date 2: 2013-06-30 13:00:23
Duration = (year (date 2) - year (date 1)) * 12 + (month (date 2) - (month date 1))
= (2013 - 2012) * 12 + (6 - 3) = 12 + 3 = 15 months
When day (day 2) < day (date 1)
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1)) - 1
Example 2:
Date 1: 2012-03-10 22:05:09
Date 2: 2013-01-09 07:19:33
Duration = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1)) - 1
= (2013 - 2012) * 12 + (1 - 3) - 1 = 12 - 2 - 1 = 9 months
When day (date 2) <= day (date 1)
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1))
Example 3.a:
Date 1: 2013-10-15 13:07:40
Date 2: 2013-10-02 10:13:59
Duration = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1))
= (2013 - 2013) * 12 - (10 - 10) = 0 months
Example 3.b:
Date 1: 2014-05-10 21:02:45
Date 2: 2013-01-08 11:13:06
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1))
= (2013 - 2014) * 12 + (1 - 5) = (-12) + (-4) = -16 months
When day (day 2) > day (date 1)
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1)) + 1
Example 4:
Date 1: 2012-06-11 12:16:17
Date 2: 2011-08-21 05:48:02
Duration (months) = (year (date 2) - year (date 1)) * 12 + (month (date 2) - month (date 1)) + 1
= (2011 - 2012) * 12 + (8 - 6) + 1 = (-12) + (2) + 1 = -9 months
In CQL, a week is defined as a duration of any time interval which starts at a certain time of day at a certain calendar day at a certain calendar week and ends at the same time of day at the same calendar day of the ending calendar week. In other words, a complete week is always seven days long.
Duration = [date 2 - date 1 (days)] / 7
Example 1:
Date 1: 2012-03-10 22:05:09
Date 2: 2012-03-20 07:19:33
Duration = [# days (month (date 1)) - day (date 1) + # days (month (date 1) + 1) + # days (month (date 1) + 2) + … + # days (month (date 2) - 1) + day (date 2)] / 7
= (20 - 10) / 7 = 10 / 7 = 1 week
Example 2:
Date 1: 2013-05-26 23:06:11
Date 2: 2013-05-12 20:25:30
Duration = [# days (month (date 1)) - day (date 1) + # days (month (date 1) + 1) + # days (month (date 1) + 2) + … + # days (month (date 2) - 1) + day (date 2)] / 7
= (12 - 26) / 7 = -14/7 = -2 weeks
In CQL, a day is defined as a duration of any time interval which starts at a certain calendar day and ends at the next calendar day (1 second to 23 hours, 59 minutes, and 59 seconds).
The duration in days between two dates will generally be given by subtracting the start calendar date from the end calendar date, respecting the time of day between the two dates.
In CQL, an hour is defined as 60 minutes. The duration in hours between two dates is the number of minutes between the two dates, divided by 60. The result is truncated to the unit.
In CQL, a minute is defined as 60 seconds. The duration in minutes between two dates is the number of seconds between the two dates, divided by 60. The result is truncated to the unit.
Difference calculations are performed by truncating the datetime values at the next precision, and then performing the corresponding duration calculation on the truncated values.
Implementations need to consider the calculation of the "difference between" two datetimes with different timezone offsets. There are multiple use cases when this might occur, but the most prevalent is likely due to Daylight Saving Time (DST).
To support the expected results in all cases, normalization of datetime values may be needed, depending on the precision used in the difference calculation. Normalization refers to adjustment of one or both of the two datetime values used in the difference calculation, if the timezone offsets are not the same, to a common timezone offset (namely, the timezone offset of the evaluation request timestamp).
When difference is calculated for hours or finer units, timezone offsets should be normalized prior to truncation to correctly consider real (actual elapsed) time. When difference is calculated for days or coarser units, however, the time components (including timezone offset) should be truncated without normalization to correctly reflect the difference in calendar days, months, and years.
Example 1:
Date 1: 2012-03-01 03:10:00
Date 2: 2012-12-31 10:10:00
Difference (years) = Duration (years) between 2012-01-01 00:00:00 and 2012-01-01 00:00:00
Difference (years) = 0
Example 2:
Date 1: 2012-12-31 03:10:00
Date 2: 2013-01-01 10:10:00
Difference (years) = Duration (years) between 2012-01-01 00:00:00 and 2013-01-01 00:00:00
Difference (years) = 1
Example 3:
Date 1: 2016-10-10 09:00:00
Date 2: 2016-10-11 11:59:00
Difference (days) = Duration (days) between 2016-10-10 00:00:00 and 2016-10-11 00:00:00
Difference (days) = 1
Example 4:
Date 1: 2016-10-10 09:00:00
Date 2: 2016-10-12 00:00:00
Difference (days) = Duration (days) between 2016-10-10 00:00:00 and 2016-10-12 00:00:00
Difference (days) = 2
Example 5:
Date 1: 2017-03-12 01:12:05.1 (-05:00)
Date 2: 2017-03-12 03:22:27.6 (-04:00)
Date 2 (normalized): 2017-03-12 02:22:27.6 (-05:00)
Difference (hours) = Duration (hours) between 2017-03-12 01:00:00.0 (-05:00) and 2017-03-12 02:00:00.0 (-05:00)
On the day that DST goes into effect, one might compare
1:00am Eastern Standard Time (EST) 2017-03-12 01:00:00.0 (-05:00) to
3:00am Eastern Daylight Time (EDT) 2017-03-12 03:00:00.0 (-04:00)
Since time "springs forward" at 2:00am, only one hour of real time has elapsed.
To calculate the "difference in hours" as 1, the second time (3:00am EDT) is normalized to the first time’s offset (making it 2:00am EST).
The difference between 1:00am EST and 2:00am EST is clearly one hour.
In a clinical setting, recognizing this as only one hour (and not two, as the clock might suggest) is important.
Example 6:
Date 1: 2017-11-05 01:30:00.0 (-04:00)
Date 2: 2017-11-05 01:15:00.0 (-05:00)
Date 2 (normalized): 2017-11-05 02:15:00.0 (-04:00)
Difference (minutes) = Duration (minutes) between 2017-11-05 01:30:00.0 (-04:00) and 2017-11-05 02:15:00.0 (-04:00)
Similarly, when DST ends, one might compare 1:30am EDT to 1:15am EST.
If we did not respect timezone offsets, the difference would appear to be -15 minutes.
Again, to calculate the "difference in minutes" as 45, the second time (1:15am EST) is normalized to the first time’s offset (making it 2:15am EDT).
The difference between 1:30am EDT and 2:15am EDT is clearly 45 minutes.
Example 7:
Date 1: 2017-03-12 00:00:00.0 (-05:00)
Date 2: 2017-03-13 00:00:00.0 (-04:00)
Without normalization of the dates:
Difference (days) = Duration (days) between 2017-03-12 00:00:00.0 and 2017-03-13 00:00:00.0
In some cases, normalization prior to truncation may give unexpected results. Consider the "difference in days" between midnight (EST) on the day that DST goes into effect and midnight (EDT) the next day. One would expect that since the second date is the "next day" on the calendar, difference in days should be 1.
With normalization prior to truncation, the second date would become 11:00pm EST on the same day as the first date.
The difference in days would be 0, which is not the expected result.
Example 8:
Date 1: 2015-05-30 12:30:14
Date 2: 2015-01-05 01:14:45
Difference (years) = Duration (years) between 2015-01-01 00:00:00 and 2015-01-01 00:00:00
Difference (years) = 0 years
Example 9:
Date 1: 2019-01-01 14:40:30
Date 2: 2018-12-31 13:30:45
Difference (years) = Duration (years) between 2019-01-01 00:00:00 and 2018:01:01 00:00:00
Difference (years) = -1 year
Example 10:
Date 1: 2013-05-30 12:15:15
Date 2: 2013-01-15 13:01:45
Difference (months) = Duration (months) between 2013-05-01 00:00:00 and 2013-01-01 00:00:00
Difference (months) = -4 months
Example 11:
Date 1: 2017-11-14 11:30:00
Date 2: 2017-11-12 13:00:00
Difference (days) = Duration (days) between 2017-11-14 00:00:00 and 2017-11-12 00:00:00
Difference (days) = -2 days
Example 12:
Date 1: 2011-06-29 15:45:59 (-5:00)
Date 2: 2011-06-29 13:30:12 (-4:00)
Date 2 (normalized): 2011-06-29 12:30:12 (-5:00)
Difference (hours) = Duration (hours) between 2011-06-29 15:00:00.0 and 2011-06-29 12:00:00.0
Difference (hours) = -3 hours
Example 13:
Date 1: 2016-10-31 04:30:17 (-4:00)
Date 2: 2016-10-31 02:45:55 (-5:00)
Date 2 (normalized): 2016-10-31 03:45:55
Difference (minutes) = Duration (minutes) between 2016-10-31 04:30:00.0 and 2016-10-31 03:45:00.0
Difference (minutes) = -45 minutes