Time arithmetic

In addition to combining numbers, addition, and subtraction, operators can operate on time values. With these extensions, you can perform time shifts or compute durations.

The pitfall here is that the effective operation performed depends on the nature (classes) of the operands: should both be of class D and the results of the subtraction is an integer, should the left be of class D and the right of class I and the result is of class D.

Consider the following expressions:

'2000-12-31' + 1
'2001-01-02' – 1
'2000-04-01 16:14' - '2000-03-30 16:15'

The first and second expressions both perform time shifts: the former computes the date of the day after the December 31 st , 2000, the later computes the date of the day before J anuary 2 nd , 2001. Both of these expressions evaluate to January 1 st , 2001. Formally, adding or subtracting an integer to or from a date shifts that date either in the future (addition) or in the past (subtraction) of the number of days stated by the right operand.

Time shifts are computed using complete days and all date and time complexity is handled. For example:

'2007-03-01 15:17'-1 evaluates to '2007-02-28 15:17'

while

'2008-03-01 15:17'-1 evaluates to '2008-02-29 15:17'

The last expression combines two dates to compute the number of complete days elapsed between the two operands, that expression evaluates to 1: only 47 hours and 59 minutes elapsed between the two dates, that is enough for one day but not for two.

Shifting or computing duration using days is usually enough. But under certain circumstances, one might appreciate to deal with seconds, minutes, hours, months or either years. This can be accomplished with the + and operators associated with one accuracy specifier. Five accuracy specifiers are available in DML, these are:

Specifier Accuracy used

s

second

m

minute

h

hour

M

month

Y

year

These specifiers are attached to the standard operators to change their accuracy. The following expressions perform time shifts using units of months, years or hours:

'2000-12-31' +M 1→'2001-01-31'
'2001-01-02 08:54' -Y 1→'2000-01-02 08:54'
'2000-04-01 16:14' +h 15 →'2000-04-01 07:14'

Time shifts using seconds, hours, or days always evaluate to an existing date in the Gregorian calendar. Shifting to the day after February 28 th might evaluate to either February 28 th or March 1 st.

Time shifts using months behave differently. When you shift one month in the future you obtain the same day in the next month: from December 14 th to January 14 th. Month shifts operate regardless of the exact count of days elapsed: shifting one month might results in 28, 29, 30 or 31 days shift. In addition, month shifts from dates after the 28 th of a month never cross month ends. Should the resulting date not a valid date in the Gregorian calendar (such as June 31 st), it is adjusted to the last day of the month. You could say that ends of months are “sticky” when computing time shifts.

The following are some examples:

'2003-01-13' +M 1 → '2003-02-13''2008-01-31' +M 3 → '2008-04-30'
'2008-01-31' +M 1 → '2008-02-29''2003-05-31' -M 3 → '2008-02-29'
'2008-01-31' +M 2 → '2008-03-31''2006-04-30' -M 1 → '2006-03-30'

Years shifts always evaluate to the same day in another year, regardless the number of days elapsed: from July 14 th, 2007 to July 14 th , 1789. Shifting one year might result in either 365 or 366 days shifts. If the obtained date is a February 29 th that does not exist, it is adjusted to February 28 th as an application of the sticky end of month paradigm.

When computing durations, the specifiers change the unit of the resulting value. For example, you can obtain the time elapsed between September 17 th , 2008 8:54 and September 18 th , 2008 8:55 in seconds, hours, months, or years, as follows:

'2008-09-18 08:55' –s '2008-09-17 08:54'→86460
'2008-09-18 08:55' –h '2008-09-17 08:54'→24
'2008-09-18 08:54' –M '2008-09-17 08:54'→0
'2008-09-18 08:54' –Y '2008-09-17 08:54'→0

The obtained value can be the number of seconds, minutes, hours, or days completely elapsed between the two dates. For durations in months and years, the value obtained is the biggest possible shift of the right operand that generates a date anterior to the left operand. This method ensures consistency with the way months shifts are processed.

Related Links