Comparing the interval between two date and time fields

Use the static DateDiff method of the DBFunction class to compute the interval between two java.util.Date fields. The DateDiff method takes two ColumnRef parameters to timestamp fields in the database: a starting timestamp and an ending timestamp. The DateDiff method returns the interval between the two fields.

DateDiff(dateDiffPart : DateDiffPart, startDate : ColumnRef, endDate : ColumnRef) : DBFunction

You use the initial parameter of the method, dateDiffPart, to specify the unit of measure for the result. You can specify DAYS, HOURS, SECONDS, or MILLISECONDS. If endDate precedes the startDate, the method returns a negative value for the interval, instead of a positive value.

Example of the DateDiff method

The following Gosu code uses the DateDiff method to compute the interval between the assigned date and the due date on an activity. The query builder code uses the returned interval in a comparison predicate to select activities with due dates less than 15 days from their assignment dates. 

uses gw.api.database.Query
uses gw.api.database.DBFunction
uses gw.api.database.QuerySelectColumns
uses gw.api.path.Paths

var query = Query.make(Activity) 
// Query for activities with due dates less than 15 days from the assigned date.
query.compare(DBFunction.DateDiff(DAYS, 
        query.getColumnRef("AssignmentDate"), query.getColumnRef("EndDate")), LessThan, 15)

// Order the result by assignment date.
for (activity in (query.select().orderBy(
        QuerySelectColumns.path(Paths.make(Activity#AssignmentDate))))) { 
  print("Assigned on " + activity.AssignmentDate + ": " + activity.DisplayName) 
}

Effect of daylight saving time on the return value of the DateDiff method

The DateDiff method does not adjust for daylight saving, or summer, time. For example, consider a locale in which daylight saving time ends on the first Sunday in November. A call to the DateDiff method requests the number of hours between the java.util.Date values 2017-11-04 12:00 and 2017-11-05 12:00. The method returns an interval of 24 hours, even though 25 hours separate the two in solar time.