datediff()

Calculates the difference between two dates in a specified time unit and returns it as an integer.

Syntax

datediff(START_DATE, END_DATE, UNIT)

Parameters

START_DATE: An expression that returns the start date.
END_DATE: An expression that returns the end date.
UNIT: A string representing the time unit for the difference calculation. The supported units are as follows.

Unit	Description
`year`	Years
`mon`	Months
`week`	Weeks
`day`	Days
`hour`	Hours
`min`	Minutes
`sec`	Seconds
`msec`	Milliseconds

Description

The datediff() function returns the difference obtained by subtracting START_DATE from END_DATE, computed in the specified unit. The return type is a 64-bit integer.

The week, day, hour, min, sec, and msec units are calculated by dividing the millisecond difference by the corresponding unit. The year and mon units are calculated based on the calendar to avoid leap-year discrepancies.

Returns null if START_DATE or END_DATE is null or not a timestamp type.

Error codes

90630: Raised when the number of arguments is not 3.
90631: Raised when the UNIT value is invalid. The field parameter contains the invalid unit.

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

Calculate the difference in hours from the access log timestamp to the current time.

table limit=5 WEB_APACHE_SAMPLE | eval diff_hour = datediff(_time, now(), "hour")
| fields _time, diff_hour

Calculate the difference from the access log timestamp to the current time in multiple units.

table limit=5 WEB_APACHE_SAMPLE
| eval diff_day = datediff(_time, now(), "day"),
       diff_hour = datediff(_time, now(), "hour"),
       diff_min = datediff(_time, now(), "min")
| fields _time, diff_day, diff_hour, diff_min

NULL input

json "{}" | eval result = datediff(null, now(), "day")
| # result: null

Compatibility

The datediff() function has been available since before Sonar 4.0.