Temporal functions - duration

Duration functions allow for the creation and manipulation of temporal DURATION values.

See also Temporal (Date/Time) values and Temporal operators.

Information regarding specifying and accessing components of a DURATION value can be found in the page about Temporal values.

Creating a DURATION from duration components

duration() can construct a DURATION from a MAP of its components in the same way as the temporal instant types.

  • years

  • quarters

  • months

  • weeks

  • days

  • hours

  • minutes

  • seconds

  • milliseconds

  • microseconds

  • nanoseconds

Syntax:

duration([ {years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, nanoseconds} ])

Returns:

DURATION

Arguments:

Name Description

A single map consisting of the following:

years

A numeric expression.

quarters

A numeric expression.

months

A numeric expression.

weeks

A numeric expression.

days

A numeric expression.

hours

A numeric expression.

minutes

A numeric expression.

seconds

A numeric expression.

milliseconds

A numeric expression.

microseconds

A numeric expression.

nanoseconds

A numeric expression.

Considerations:

At least one parameter must be provided (duration() and duration({}) are invalid).

There is no constraint on how many of the parameters are provided.

It is possible to have a DURATION where the amount of a smaller unit (e.g. seconds) exceeds the threshold of a larger unit (e.g. days).

The values of the parameters may be expressed as decimal fractions.

The values of the parameters may be arbitrarily large.

The values of the parameters may be negative.
Example 1. duration()
Query
UNWIND [
duration({days: 14, hours:16, minutes: 12}),
duration({months: 5, days: 1.5}),
duration({months: 0.75}),
duration({weeks: 2.5}),
duration({minutes: 1.5, seconds: 1, milliseconds: 123, microseconds: 456, nanoseconds: 789}),
duration({minutes: 1.5, seconds: 1, nanoseconds: 123456789})
] AS aDuration
RETURN aDuration
Table 1. Result
aDuration

P14DT16H12M

P5M1DT12H

P22DT19H51M49.5S

P17DT12H

PT1M31.123456789S

PT1M31.123456789S

Rows: 6

Creating a DURATION from a string

duration() returns the DURATION value obtained by parsing a STRING representation of a temporal amount.

Syntax:

duration(temporalAmount)

Returns:

DURATION

Arguments:

Name Description

temporalAmount

A STRING representing a temporal amount.

Considerations:

temporalAmount must comply with either the unit based form or date-and-time based form defined for DURATION values.
Example 2. duration()
Query
UNWIND [
duration("P14DT16H12M"),
duration("P5M1.5D"),
duration("P0.75M"),
duration("PT0.75M"),
duration("P2012-02-02T14:37:21.545")
] AS aDuration
RETURN aDuration
Table 2. Result
aDuration

P14DT16H12M

P5M1DT12H

P22DT19H51M49.5S

PT45S

P2012Y2M2DT14H37M21.545S

Rows: 5

Computing the DURATION between two temporal instants

duration() has sub-functions which compute the logical difference (in days, months, etc) between two temporal instant values:

  • duration.between(a, b): Computes the difference in multiple components between instant a and instant b. This captures month, days, seconds and sub-seconds differences separately.

  • duration.inMonths(a, b): Computes the difference in whole months (or quarters or years) between instant a and instant b. This captures the difference as the total number of months. Any difference smaller than a whole month is disregarded.

  • duration.inDays(a, b): Computes the difference in whole days (or weeks) between instant a and instant b. This captures the difference as the total number of days. Any difference smaller than a whole day is disregarded.

  • duration.inSeconds(a, b): Computes the difference in seconds (and fractions of seconds, or minutes or hours) between instant a and instant b. This captures the difference as the total number of seconds.

duration.between()

duration.between() returns the DURATION value equal to the difference between the two given instants.

Syntax:

duration.between(instant1, instant2)

Returns:

DURATION

Arguments:

Name Description

instant1

An expression returning any temporal instant type (DATE etc.) that represents the starting instant.

instant2

An expression returning any temporal instant type (DATE etc.) that represents the ending instant.

Considerations:

If instant2 occurs earlier than instant1, the resulting DURATION will be negative.

If instant1 has a time component and instant2 does not, the time component of instant2 is assumed to be midnight, and vice versa.

If instant1 has a time zone component and instant2 does not, the time zone component of instant2 is assumed to be the same as that of instant1, and vice versa.

If instant1 has a date component and instant2 does not, the date component of instant2 is assumed to be the same as that of instant1, and vice versa.
Example 3. duration.between()
Query
UNWIND [
duration.between(date("1984-10-11"), date("1985-11-25")),
duration.between(date("1985-11-25"), date("1984-10-11")),
duration.between(date("1984-10-11"), datetime("1984-10-12T21:40:32.142+0100")),
duration.between(date("2015-06-24"), localtime("14:30")),
duration.between(localtime("14:30"), time("16:30+0100")),
duration.between(localdatetime("2015-07-21T21:40:32.142"), localdatetime("2016-07-21T21:45:22.142")),
duration.between(datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/Stockholm'}), datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/London'}))
] AS aDuration
RETURN aDuration
Table 3. Result
aDuration

P1Y1M14D

P-1Y-1M-14D

P1DT21H40M32.142S

PT14H30M

PT2H

P1YT4M50S

PT1H

Rows: 7

duration.inMonths()

duration.inMonths() returns the DURATION value equal to the difference in whole months, quarters or years between the two given instants.

Syntax:

duration.inMonths(instant1, instant2)

Returns:

DURATION

Arguments:

Name Description

instant1

An expression returning any temporal instant type (DATE etc.) that represents the starting instant.

instant2

An expression returning any temporal instant type (DATE etc.) that represents the ending instant.

Considerations:

If instant2 occurs earlier than instant1, the resulting DURATION will be negative.

If instant1 has a time component and instant2 does not, the time component of instant2 is assumed to be midnight, and vice versa.

If instant1 has a time zone component and instant2 does not, the time zone component of instant2 is assumed to be the same as that of instant1, and vice versa.

If instant1 has a date component and instant2 does not, the date component of instant2 is assumed to be the same as that of instant1, and vice versa.

Any difference smaller than a whole month is disregarded.
Example 4. duration.inMonths()
Query
UNWIND [
duration.inMonths(date("1984-10-11"), date("1985-11-25")),
duration.inMonths(date("1985-11-25"), date("1984-10-11")),
duration.inMonths(date("1984-10-11"), datetime("1984-10-12T21:40:32.142+0100")),
duration.inMonths(date("2015-06-24"), localtime("14:30")),
duration.inMonths(localdatetime("2015-07-21T21:40:32.142"), localdatetime("2016-07-21T21:45:22.142")),
duration.inMonths(datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/Stockholm'}), datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/London'}))
] AS aDuration
RETURN aDuration
Table 4. Result
aDuration

P1Y1M

P-1Y-1M

PT0S

PT0S

P1Y

PT0S

Rows: 6

duration.inDays()

duration.inDays() returns the DURATION value equal to the difference in whole days or weeks between the two given instants.

Syntax:

duration.inDays(instant1, instant2)

Returns:

DURATION

Arguments:

Name Description

instant1

An expression returning any temporal instant type (DATE etc.) that represents the starting instant.

instant2

An expression returning any temporal instant type (DATE etc.) that represents the ending instant.

Considerations:

If instant2 occurs earlier than instant1, the resulting DURATION will be negative.

If instant1 has a time component and instant2 does not, the time component of instant2 is assumed to be midnight, and vice versa.

If instant1 has a time zone component and instant2 does not, the time zone component of instant2 is assumed to be the same as that of instant1, and vice versa.

If instant1 has a date component and instant2 does not, the date component of instant2 is assumed to be the same as that of instant1, and vice versa.

Any difference smaller than a whole day is disregarded.
Example 5. duration.inDays()
Query
UNWIND [
duration.inDays(date("1984-10-11"), date("1985-11-25")),
duration.inDays(date("1985-11-25"), date("1984-10-11")),
duration.inDays(date("1984-10-11"), datetime("1984-10-12T21:40:32.142+0100")),
duration.inDays(date("2015-06-24"), localtime("14:30")),
duration.inDays(localdatetime("2015-07-21T21:40:32.142"), localdatetime("2016-07-21T21:45:22.142")),
duration.inDays(datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/Stockholm'}), datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/London'}))
] AS aDuration
RETURN aDuration
Table 5. Result
aDuration

P410D

P-410D

P1D

PT0S

P366D

PT0S

Rows: 6

duration.inSeconds()

duration.inSeconds() returns the DURATION value equal to the difference in seconds and fractions of seconds, or minutes or hours, between the two given instants.

Syntax:

duration.inSeconds(instant1, instant2)

Returns:

DURATION

Arguments:

Name Description

instant1

An expression returning any temporal instant type (DATE etc.) that represents the starting instant.

instant2

An expression returning any temporal instant type (DATE etc.) that represents the ending instant.

Considerations:

If instant2 occurs earlier than instant1, the resulting DURATION will be negative.

If instant1 has a time component and instant2 does not, the time component of instant2 is assumed to be midnight, and vice versa.

If instant1 has a time zone component and instant2 does not, the time zone component of instant2 is assumed to be the same as that of instant1, and vice versa.

If instant1 has a date component and instant2 does not, the date component of instant2 is assumed to be the same as that of instant1, and vice versa.
Example 6. duration.inSeconds()
Query
UNWIND [
duration.inSeconds(date("1984-10-11"), date("1984-10-12")),
duration.inSeconds(date("1984-10-12"), date("1984-10-11")),
duration.inSeconds(date("1984-10-11"), datetime("1984-10-12T01:00:32.142+0100")),
duration.inSeconds(date("2015-06-24"), localtime("14:30")),
duration.inSeconds(datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/Stockholm'}), datetime({year: 2017, month: 10, day: 29, hour: 0, timezone: 'Europe/London'}))
] AS aDuration
RETURN aDuration
Table 6. Result
aDuration

PT24H

PT-24H

PT25H32.142S

PT14H30M

PT1H

Rows: 5