DateTimeDiff (NoSQL query)

  • Article

APPLIES TO: NoSQL

Returns the difference, as a signed integer, of the specified date and time part between two date and time values.

Syntax

DateTimeDiff(<date_time_part>, <start_date_time>, <end_date_time>)

Arguments

Description
date_time_part A string representing a part of an ISO 8601 date format specification. This part is used to indicate which aspect of the date to compare.
start_date_time A Coordinated Universal Time (UTC) date and time string in the ISO 8601 format YYYY-MM-DDThh:mm:ss.fffffffZ.
end_date_time A Coordinated Universal Time (UTC) date and time string in the ISO 8601 format YYYY-MM-DDThh:mm:ss.fffffffZ.

Note

For more information on the ISO 8601 format, see ISO 8601.

Return types

Returns a numeric value that is a signed integer.

Examples

The following examples compare February 4, 2019 16:00 UTC and March 5, 2018 05:00 UTC using various date and time parts.

SELECT VALUE {
    diffPastYears: DateTimeDiff("yyyy", "2019-02-04T16:00:00.0000000", "2018-03-05T05:00:00.0000000"),
    diffPastMonths: DateTimeDiff("mm", "2019-02-04T16:00:00.0000000", "2018-03-05T05:00:00.0000000"),
    diffPastDays: DateTimeDiff("dd", "2019-02-04T16:00:00.0000000", "2018-03-05T05:00:00.0000000"),
    diffPastHours: DateTimeDiff("hh", "2019-02-04T16:00:00.0000000", "2018-03-05T05:00:00.0000000"),
    diffPastSeconds: DateTimeDiff("ss", "2019-02-04T16:00:00.0000000", "2018-03-05T05:00:00.0000000"),
    diffFutureYears: DateTimeDiff("yyyy", "2018-03-05T05:00:00.0000000", "2019-02-04T16:00:00.0000000"),
    diffFutureMonths: DateTimeDiff("mm", "2018-03-05T05:00:00.0000000", "2019-02-04T16:00:00.0000000"),
    diffFutureDays: DateTimeDiff("dd", "2018-03-05T05:00:00.0000000", "2019-02-04T16:00:00.0000000"),
    diffFutureHours: DateTimeDiff("hh", "2018-03-05T05:00:00.0000000", "2019-02-04T16:00:00.0000000"),
    diffFutureSeconds: DateTimeDiff("ss", "2018-03-05T05:00:00.0000000", "2019-02-04T16:00:00.0000000")
}

[
  {
    "diffPastYears": -1,
    "diffPastMonths": -11,
    "diffPastDays": -336,
    "diffPastHours": -8075,
    "diffPastSeconds": -29070000,
    "diffFutureYears": 1,
    "diffFutureMonths": 11,
    "diffFutureDays": 336,
    "diffFutureHours": 8075,
    "diffFutureSeconds": 29070000
  }
]

Remarks

  • This function returns undefined for these reasons:
    • The specified date and time part is invalid.
    • The date and time in either start or end argument isn't a valid ISO 8601 date and time string.
  • The ISO 8601 date format specifies valid date and time parts to use with this function: | | Format | | --- | --- | | Day | day, dd, d | | Hour | hour, hh | | Minute | minute, mi, n | | Second | second, ss, s | | Millisecond | millisecond, ms | | Microsecond | microsecond, mcs | | Nanosecond | nanosecond, ns |
  • The function always returns a signed integer value. The function returns a measurement of the number of boundaries crossed for the specified date and time part, not a measurement of the time interval.

Related content