Ask Reuben – May 15, 2025

INTERVAL

How do you compare the difference between two times? 

What is the INTERVAL data-type? 

Why is there a DATETIME widget but no INTERVAL widget? 

Why are there two classes of INTERVAL?

I saw some “interesting” code recently where a developer was attempting to calculate the difference between two date time variables. They were converting the DATETIME into a STRING, taking substrings and then had some IF’s, some “59”‘s, some “23”‘s,  to cater for the case where the two DATETIME overlapped midnight. etc. They were not aware of the INTERVAL datatype and INTERVAL literals.

The INTERVAL datatype stores periods of time as Year/Months or Day/Hour/Minute/Seconds/Fractions.  The reason there are these two distinctions is that there a variable number of days in each month.  There is 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day, and 12 months in a year, but the  number of days in a given month varies.  How long is a month and 10 days?  Hence an interval cannot have a definition that spreads across the gap between months and days.

An INTERVAL datatype is similar to a DATETIME datatype in that it has the two from/to qualifiers from YEAR, MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION .  There are two main differences between DATETIME and INTERVAL in this regard…

  1. because of the variable number of days in a month, the qualifiers for an INTERVAL can’t overlap months and days,  hence the two distinct INTERVAL types.
  2. the value of the first unit of precision in an interval can have upto 9 significant digits.  For an INTERVAL the first unit of precision can exceed 12 months, 24 hours, 60 minutes, 60 seconds values.  It is possible to have 13 months in an interval that is measured in months to months, 32 days in an interval that is measured in days to something, 25 hours in an interval that is measured in hours to something, 61 minutes in an interval that is measured in minutes to something, 61 seconds in an interval that is measured in seconds to something,

Only the first unit of an interval can exceed the datetime equivalent maximum.   With an INTERVAL we would say “25 hours and 1 minutes”, we would not say 23 hours and 121 minutes”.

The reason that this first unit of an interval can exceed the datetime equivalent is the reason that you typically can not use DATEEDIT, DATETIME , TIMEEDIT widgets with INTERVAL datatypes. They typically don’t cater for the fact this first unit can be bigger than the datetime limit and can have upto 9 digits.  Hopefully with Universal Rendering, we can one day consider implementing our own GUI INTERVALEDIT widget if there is sufficient demand.  It is rare to input an INTERVAL, it is normally displayed, hence the lack of demand to date.

The use of an INTERVAL literal allows you to explicitly specify INTERVAL values in your code. With a defintion of

DEFINE i INTERVAL SECOND TO FRACTION(2)

you can assign it via either of these code techniques …

LET i = INTERVAL(5.00) SECOND TO FRACTION(2)

or you can use a STRING and rely on the Data Type conversion rules to turn your correctly formatted string into an INTERVAL

LET i = "5.00"

or you can use UNITS operator to convert an INTEGER to an INTERVAL

LET i = 5 UNITS SECOND

The rules of Datetime Expression and Interval Expression allow for …

  • DATETIME + INTERVAL = DATETIME
  • DATETIME – INTERVAL = DATETIME
  • DATETIME – DATETIME = INTERVAL

A good little program to experiment with interval literal and interval is expression is the following one …


MAIN
DEFINE l_start_datetime, l_now_datetime DATETIME YEAR TO FRACTION(2)
DEFINE l_refresh_interval, l_elapsed_interval INTERVAL SECOND TO FRACTION(2) 

    -- Experiment with different interval values
    #LET l_refresh_interval = INTERVAL(1.00) SECOND TO FRACTION(2)
    #LET l_refresh_interval = INTERVAL(0.10) SECOND TO FRACTION(2)
    LET l_refresh_interval = INTERVAL(0.04) SECOND TO FRACTION(2)
    #LET l_refresh_interval = INTERVAL(0.01) SECOND TO FRACTION(2)

    MENU ""
        ON ACTION go
            LET l_start_datetime = CURRENT
            WHILE TRUE
                LET l_now_datetime = CURRENT
                LET l_elapsed_interval = l_now_datetime - l_start_datetime
                IF l_elapsed_interval >= l_refresh_interval THEN
                    MESSAGE l_now_datetime
                    CALL ui.Interface.refresh()
                    LET l_start_datetime = l_now_datetime
                END IF
            END WHILE
    END MENU
END MAIN

… the Interval literal l_refresh_interval is how long you will wait until refreshing the screen, and the Interval expression l_elapsed_interval calculates the elapsed time between now and the last the screen was refreshed.  As per my comment in this article on ui.Interface.refresh I don’t think you want to refresh the screen more than 24 times a second (the same rate as used in movies), an interval of 0.04 second is close to 24 times a second.  Chances are you will find the other values of 0.1 or 1.0 second more than adequate as the rate to refresh a Genero screen as it progresses through a long process.  if you don’t mind the processing power used in constantly calculating the elapsed interval, this is a technique that gives a good update of the screen without overloading your network with screen updates.

If you find yourself doing some complex code with datetime, interval, addition and subtraction, step back and making sure you are utilising the functionality of the interval datatype.