Datetime Addition

With DATETIME variables, a common requirement is to add a number of minutes to a DATETIME.  Developers occasionally get stuck and the reason I believe is that Implicit Type conversions can get you into bad habits.  Sometimes you need to take a step back, use 3 lines of code instead of 1, then you can see the issue, and then see what the 1 line of code solution is.

The implicit conversion that gets you into trouble is something like

DEFINE l_10_minutes INTERVAL MINUTE TO MINUTE
LET l_10_minutes = "10"

The right hand side is a text literal and it is also a well formatted interval value in the “hh:mm:ss.ffff” format, with no hours, seconds, or fraction thereof, and so the runtime is able to successfully assign the text literal to the interval variable intended.  It is expecting the text to be of the form “mm”, no colons, dashes etc and so is able to take the text literal “10” and cast it to an interval of 10 minutes.

A developers instinct then when having to use this in another expression (in this example finding 10 minutes from now) is then  to do something like …

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND
LET l_10_minutes_from_now = CURRENT + "10"

… which will not work.   How can the runtime know that “10” in this instance is an interval of 10 minutes?

A technique with a few extra lines of code is to explicitly define an interval variable and use that in the expression …

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND 
DEFINE l_10_minutes INTERVAL MINUTE TO MINUTES
LET l_10_minutes = "10"
LET l_10_minutes_from_now = CURRENT + l_10_minutes

With the introduction of CONSTANT in 1.10, a better solution is to use a CONSTANT instead of a variable so …

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND 
CONSTANT c_10_minutes INTERVAL MINUTE TO MINUTE = INTERVAL(10) MINUTE TO MINUTE
LET l_10_minutes_from_now = CURRENT + c_10_minutes

This shows what is known as an Interval Literal.   This could also have been used in the variable example instead of relying on the implicit conversion …

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND 
DEFINE l_10_minutes INTERVAL MINUTE TO MINUTES
LET l_10_minutes = INTERVAL(10) MINUTE TO MINUTE
LET l_10_minutes_from_now = CURRENT + l_10_minutes

This use of an Interval Literal then shows how the original problem could be coded in 1 line ..

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND 
LET l_10_minutes_from_now = CURRENT + INTERVAL(10) MINUTE TO MINUTE

Updated (11th April 2022) – added this paragraph on UNITS

This INTERVAL … MINUTE to MINUTE syntax is wordy and long.  This can be shortened using the UNITS syntax which is an operator that converts an INTEGER to an INTERVAL.  This UNITS syntax is handy where the INTERVAL is measured by only one qualifier and it makes the code read better in my opinion.

DEFINE l_10_minutes_from_now DATETIME YEAR TO SECOND 
LET l_10_minutes_from_now = CURRENT + 10 UNITS MINUTE

The documentation has a two sections on Literals and Expressions as they relate to the various datatypes.

Genero’s implicit type conversion can be very useful but there are times you need to make sure you are using the correct datatype.

The following example can be run to see the various techniques in operation

MAIN

    DEFINE l_10_minutes_string INTERVAL MINUTE TO MINUTE
    DEFINE l_10_minutes_interval INTERVAL MINUTE TO MINUTE

    CONSTANT c_10_minutes INTERVAL MINUTE TO MINUTE = INTERVAL(10) MINUTE TO MINUTE

    LET l_10_minutes_string = "10"
    LET l_10_minutes_interval = INTERVAL(10) MINUTE TO MINUTE

    DISPLAY "Now ........................... = ", CURRENT
    DISPLAY "*** Add 10 minutes using various techniques ***"

    DISPLAY "Assigning as a text-literal .... ", CURRENT + l_10_minutes_string
    DISPLAY "Using a constant ................ ", CURRENT + c_10_minutes
    DISPLAY "Assigning as an interval ....... ", CURRENT + l_10_minutes_interval
    DISPLAY "Using an interval literal ........", CURRENT + INTERVAL(10) MINUTE TO MINUTE
    DISPLAY "Using UNITS .....................", CURRENT + 10 UNITS MINUTE

    DISPLAY "*** Add 10 minutes using wrong technique ***"
    DISPLAY "Adding a string ...... ", CURRENT + "10"

END MAIN