Temporal functions - instant types

Cypher provides functions allowing for the creation and manipulation of values for each temporal type — Date, Time, LocalTime, DateTime, and LocalDateTime.

Functions:

1. Temporal instant types

An introduction to temporal instant types, including descriptions of creation functions, clocks, and truncation.

1.1. An overview of temporal instant type creation

Each function bears the same name as the type, and construct the type they correspond to in one of four ways:

  • Capturing the current time

  • Composing the components of the type

  • Parsing a string representation of the temporal value

  • Selecting and composing components from another temporal value by

    • either combining temporal values (such as combining a Date with a Time to create a DateTime), or

    • selecting parts from a temporal value (such as selecting the Date from a DateTime); the extractors — groups of components which can be selected — are:

      • date — contains all components for a Date (conceptually year, month and day).

      • time — contains all components for a Time (hour, minute, second, and sub-seconds; namely millisecond, microsecond and nanosecond). If the type being created and the type from which the time component is being selected both contain timezone (and a timezone is not explicitly specified) the timezone is also selected.

      • datetime — selects all components, and is useful for overriding specific components. Analogously to time, if the type being created and the type from which the time component is being selected both contain timezone (and a timezone is not explicitly specified) the timezone is also selected.

    • In effect, this allows for the conversion between different temporal types, and allowing for 'missing' components to be specified.

Table 1. Temporal instant type creation functions
Function Date Time LocalTime DateTime LocalDateTime

Getting the current value

X

X

X

X

X

Creating a calendar-based (Year-Month-Day) value

X

X

X

Creating a week-based (Year-Week-Day) value

X

X

X

Creating a quarter-based (Year-Quarter-Day) value

X

X

X

Creating an ordinal (Year-Day) value

X

X

X

Creating a value from time components

X

X

Creating a value from other temporal values using extractors (i.e. converting between different types)

X

X

X

X

X

Creating a value from a string

X

X

X

X

X

Creating a value from a timestamp

X

All the temporal instant types — including those that do not contain time zone information support such as Date, LocalTime and DateTime — allow for a time zone to specified for the functions that retrieve the current instant. This allows for the retrieval of the current instant in the specified time zone.

1.2. Controlling which clock to use

The functions which create temporal instant values based on the current instant use the statement clock as default. However, there are three different clocks available for more fine-grained control:

  • transaction: The same instant is produced for each invocation within the same transaction. A different time may be produced for different transactions.

  • statement: The same instant is produced for each invocation within the same statement. A different time may be produced for different statements within the same transaction.

  • realtime: The instant produced will be the live clock of the system.

The following table lists the different sub-functions for specifying the clock to be used when creating the current temporal instant value:

Type default transaction statement realtime

Date

date()

date.transaction()

date.statement()

date.realtime()

Time

time()

time.transaction()

time.statement()

time.realtime()

LocalTime

localtime()

localtime.transaction()

localtime.statement()

localtime.realtime()

DateTime

datetime()

datetime.transaction()

datetime.statement()

datetime.realtime()

LocalDateTime

localdatetime()

localdatetime.transaction()

localdatetime.statement()

localdatetime.realtime()

1.3. Truncating temporal values

A temporal instant value can be created by truncating another temporal instant value at the nearest preceding point in time at a specified component boundary (namely, a truncation unit). A temporal instant value created in this way will have all components which are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components.

The following truncation units are supported:

  • millennium: Select the temporal instant corresponding to the millenium of the given instant.

  • century: Select the temporal instant corresponding to the century of the given instant.

  • decade: Select the temporal instant corresponding to the decade of the given instant.

  • year: Select the temporal instant corresponding to the year of the given instant.

  • weekYear: Select the temporal instant corresponding to the first day of the first week of the week-year of the given instant.

  • quarter: Select the temporal instant corresponding to the quarter of the year of the given instant.

  • month: Select the temporal instant corresponding to the month of the given instant.

  • week: Select the temporal instant corresponding to the week of the given instant.

  • day: Select the temporal instant corresponding to the month of the given instant.

  • hour: Select the temporal instant corresponding to the hour of the given instant.

  • minute: Select the temporal instant corresponding to the minute of the given instant.

  • second: Select the temporal instant corresponding to the second of the given instant.

  • millisecond: Select the temporal instant corresponding to the millisecond of the given instant.

  • microsecond: Select the temporal instant corresponding to the microsecond of the given instant.

The following table lists the supported truncation units and the corresponding sub-functions:

Truncation unit Date Time LocalTime DateTime LocalDateTime

millennium

date.truncate('millennium' input)

datetime.truncate('millennium' input)

localdatetime.truncate('millennium' input)

century

date.truncate('century' input)

datetime.truncate('century' input)

localdatetime.truncate('century' input)

decade

date.truncate('decade' input)

datetime.truncate('decade' input)

localdatetime.truncate('decade' input)

year

date.truncate('year' input)

datetime.truncate('year' input)

localdatetime.truncate('year' input)

weekYear

date.truncate('weekYear' input)

datetime.truncate('weekYear' input)

localdatetime.truncate('weekYear' input)

quarter

date.truncate('quarter' input)

datetime.truncate('quarter' input)

localdatetime.truncate('quarter' input)

month

date.truncate('month' input)

datetime.truncate('month' input)

localdatetime.truncate('month' input)

week

date.truncate('week' input)

datetime.truncate('week' input)

localdatetime.truncate('week' input)

day

date.truncate('day' input)

time.truncate('day' input)

localtime.truncate('day' input)

datetime.truncate('day' input)

localdatetime.truncate('day' input)

hour

time.truncate('hour' input)

localtime.truncate('hour' input)

datetime.truncate('hour' input)

localdatetime.truncate('hour’input)

minute

time.truncate('minute' input)

localtime.truncate('minute' input)

datetime.truncate('minute' input)

localdatetime.truncate('minute' input)

second

time.truncate('second' input)

localtime.truncate('second' input)

datetime.truncate('second' input)

localdatetime.truncate('second' input)

millisecond

time.truncate('millisecond' input)

localtime.truncate('millisecond' input)

datetime.truncate('millisecond' input)

localdatetime.truncate('millisecond' input)

microsecond

time.truncate('microsecond' input)

localtime.truncate('microsecond' input)

datetime.truncate('microsecond' input)

localdatetime.truncate('microsecond' input)

2. Date: date()

Details for using the date() function.

2.1. Getting the current Date

date() returns the current Date value. If no time zone parameter is specified, the local time zone will be used.

Syntax: date([ +{timezone}+ ])

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, date() must be invoked (date({}) is invalid).

Query
RETURN date() AS currentDate

The current date is returned.

Table 2. Result
currentDate

2020-12-02

1 row

Query
RETURN date({ timezone: 'America/Los Angeles' }) AS currentDateInLA

The current date in California is returned.

Table 3. Result
currentDateInLA

2020-12-02

1 row

2.1.1. date.transaction()

date.transaction() returns the current Date value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: date.transaction([ +{timezone}+ ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN date.transaction() AS currentDate
Table 4. Result
currentDate

2020-12-02

1 row

2.1.2. date.statement()

date.statement() returns the current Date value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: date.statement([ +{timezone}+ ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN date.statement() AS currentDate
Table 5. Result
currentDate

2020-12-02

1 row

2.1.3. date.realtime()

date.realtime() returns the current Date value using the realtime clock. This value will be the live clock of the system.

Syntax: date.realtime([ +{timezone}+ ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN date.realtime() AS currentDate
Table 6. Result
currentDate

2020-12-02

1 row

Query
RETURN date.realtime('America/Los Angeles') AS currentDateInLA
Table 7. Result
currentDateInLA

2020-12-02

1 row

2.2. Creating a calendar (Year-Month-Day) Date

date() returns a Date value with the specified year, month and day component values.

Syntax: date({year [, month, day]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

Considerations:

The day of the month component will default to 1 if day is omitted.

The month component will default to 1 if month is omitted.

If month is omitted, day must also be omitted.

Query
UNWIND [
  date({year:1984, month:10, day:11}),
  date({year:1984, month:10}),
  date({year:1984})
] as theDate
RETURN theDate
Table 8. Result
theDate

1984-10-11

1984-10-01

1984-01-01

3 rows

2.3. Creating a week (Year-Week-Day) Date

date() returns a Date value with the specified year, week and dayOfWeek component values.

Syntax: date({year [, week, dayOfWeek]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

Considerations:

The day of the week component will default to 1 if dayOfWeek is omitted.

The week component will default to 1 if week is omitted.

If week is omitted, dayOfWeek must also be omitted.

Query
UNWIND [
  date({year:1984, week:10, dayOfWeek:3}),
  date({year:1984, week:10}),
  date({year:1984})
] as theDate
RETURN theDate
Table 9. Result
theDate

1984-03-07

1984-03-05

1984-01-01

3 rows

2.4. Creating a quarter (Year-Quarter-Day) Date

date() returns a Date value with the specified year, quarter and dayOfQuarter component values.

Syntax: date({year [, quarter, dayOfQuarter]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

Considerations:

The day of the quarter component will default to 1 if dayOfQuarter is omitted.

The quarter component will default to 1 if quarter is omitted.

If quarter is omitted, dayOfQuarter must also be omitted.

Query
UNWIND [
  date({year:1984, quarter:3, dayOfQuarter: 45}),
  date({year:1984, quarter:3}),
  date({year:1984})
] as theDate
RETURN theDate
Table 10. Result
theDate

1984-08-14

1984-07-01

1984-01-01

3 rows

2.5. Creating an ordinal (Year-Day) Date

date() returns a Date value with the specified year and ordinalDay component values.

Syntax: date({year [, ordinalDay]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

Considerations:

The ordinal day of the year component will default to 1 if ordinalDay is omitted.

Query
UNWIND [
  date({year:1984, ordinalDay:202}),
  date({year:1984})
] as theDate
RETURN theDate

The date corresponding to 11 February 1984 is returned.

Table 11. Result
theDate

1984-07-20

1984-01-01

2 rows

2.6. Creating a Date from a string

date() returns the Date value obtained by parsing a string representation of a temporal value.

Syntax: date(temporalValue)

Returns:

A Date.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for dates.

temporalValue must denote a valid date; i.e. a temporalValue denoting 30 February 2001 is invalid.

date(null) returns null.

Query
UNWIND [
  date('2015-07-21'),
  date('2015-07'),
  date('201507'),
  date('2015-W30-2'),
  date('2015202'),
  date('2015')
] as theDate
RETURN theDate
Table 12. Result
theDate

2015-07-21

2015-07-01

2015-07-01

2015-07-21

2015-07-21

2015-01-01

6 rows

2.7. Creating a Date using other temporal values as components

date() returns the Date value obtained by selecting and composing components from another temporal value. In essence, this allows a DateTime or LocalDateTime value to be converted to a Date, and for "missing" components to be provided.

Syntax: date({date [, year, month, day, week, dayOfWeek, quarter, dayOfQuarter, ordinalDay]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

date

A Date value.

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of date.

date(dd) may be written instead of date({date: dd}).

Query
UNWIND [
  date({year:1984, month:11, day:11}),
  localdatetime({year:1984, month:11, day:11, hour:12, minute:31, second:14}),
  datetime({year:1984, month:11, day:11, hour:12, timezone: '+01:00'})
] as dd
RETURN date({date: dd}) AS dateOnly,
   date({date: dd, day: 28}) AS dateDay
Table 13. Result
dateOnly dateDay

1984-11-11

1984-11-28

1984-11-11

1984-11-28

1984-11-11

1984-11-28

3 rows

2.8. Truncating a Date

date.truncate() returns the Date value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the Date returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, day — with some value x — may be provided when the truncation unit is year in order to ensure the returned value has the day set to x instead of the default day (which is 1).

Syntax: date.truncate(unit [, temporalInstantValue [, mapOfComponents ] ])

Returns:

A Date.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {millennium, century, decade, year, weekYear, quarter, month, week, day}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Date}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit.

Considerations:

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'day', mapOfComponents cannot contain information pertaining to a month.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

If temporalInstantValue is not provided, it will be set to the current date, i.e. date.truncate(unit) is equivalent of date.truncate(unit, date()).

Query
WITH datetime({year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+01:00'}) AS d
RETURN date.truncate('millennium', d) AS truncMillenium,
       date.truncate('century', d) AS truncCentury,
       date.truncate('decade', d) AS truncDecade,
       date.truncate('year', d, {day:5}) AS truncYear,
       date.truncate('weekYear', d) AS truncWeekYear,
       date.truncate('quarter', d) AS truncQuarter,
       date.truncate('month', d) AS truncMonth,
       date.truncate('week', d, {dayOfWeek:2}) AS truncWeek,
       date.truncate('day', d) AS truncDay
Table 14. Result
truncMillenium truncCentury truncDecade truncYear truncWeekYear truncQuarter truncMonth truncWeek truncDay

2000-01-01

2000-01-01

2010-01-01

2017-01-05

2017-01-02

2017-10-01

2017-11-01

2017-11-07

2017-11-11

1 row

3. DateTime: datetime()

Details for using the datetime() function.

3.1. Getting the current DateTime

datetime() returns the current DateTime value. If no time zone parameter is specified, the default time zone will be used.

Syntax: datetime([ +{timezone}+ ])

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, datetime() must be invoked (datetime({}) is invalid).

Query
RETURN datetime() AS currentDateTime

The current date and time using the local time zone is returned.

Table 15. Result
currentDateTime

2020-12-02T16:03:09.616Z

1 row

Query
RETURN datetime({ timezone: 'America/Los Angeles' }) AS currentDateTimeInLA

The current date and time of day in California is returned.

Table 16. Result
currentDateTimeInLA

2020-12-02T08:03:09.630-08:00[America/Los_Angeles]

1 row

3.1.1. datetime.transaction()

datetime.transaction() returns the current DateTime value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: datetime.transaction([ +{timezone}+ ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN datetime.transaction() AS currentDateTime
Table 17. Result
currentDateTime

2020-12-02T16:03:09.645Z

1 row

Query
RETURN datetime.transaction('America/Los Angeles') AS currentDateTimeInLA
Table 18. Result
currentDateTimeInLA

2020-12-02T08:03:09.659-08:00[America/Los_Angeles]

1 row

3.1.2. datetime.statement()

datetime.statement() returns the current DateTime value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: datetime.statement([ +{timezone}+ ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN datetime.statement() AS currentDateTime
Table 19. Result
currentDateTime

2020-12-02T16:03:09.673Z

1 row

3.1.3. datetime.realtime()

datetime.realtime() returns the current DateTime value using the realtime clock. This value will be the live clock of the system.

Syntax: datetime.realtime([ +{timezone}+ ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN datetime.realtime() AS currentDateTime
Table 20. Result
currentDateTime

2020-12-02T16:03:09.705015Z

1 row

3.2. Creating a calendar (Year-Month-Day) DateTime

datetime() returns a DateTime value with the specified year, month, day, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, month, day, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The month component will default to 1 if month is omitted.

The day of the month component will default to 1 if day is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, month, day, hour, minute, and second may be omitted; i.e. it is possible to specify only year, month and day, but specifying year, month, day and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789}),
  datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 645, timezone: '+01:00'}),
  datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, timezone: '+01:00'}),
  datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14}),
  datetime({year:1984, month:10, day:11, hour:12, minute:31, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, month:10, day:11, hour:12, timezone: '+01:00'}),
  datetime({year:1984, month:10, day:11, timezone: 'Europe/Stockholm'})
] as theDate
RETURN theDate
Table 21. Result
theDate

1984-10-11T12:31:14.123456789Z

1984-10-11T12:31:14.645+01:00

1984-10-11T12:31:14.645876123+01:00[Europe/Stockholm]

1984-10-11T12:31:14+01:00

1984-10-11T12:31:14Z

1984-10-11T12:31+01:00[Europe/Stockholm]

1984-10-11T12:00+01:00

1984-10-11T00:00+01:00[Europe/Stockholm]

8 rows

3.3. Creating a week (Year-Week-Day) DateTime

datetime() returns a DateTime value with the specified year, week, dayOfWeek, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, week, dayOfWeek, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The week component will default to 1 if week is omitted.

The day of the week component will default to 1 if dayOfWeek is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, week, dayOfWeek, hour, minute, and second may be omitted; i.e. it is possible to specify only year, week and dayOfWeek, but specifying year, week, dayOfWeek and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, millisecond: 645}),
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}),
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14}),
  datetime({year:1984, week:10, dayOfWeek:3, hour:12, timezone: '+01:00'}),
  datetime({year:1984, week:10, dayOfWeek:3, timezone: 'Europe/Stockholm'})
] as theDate
RETURN theDate
Table 22. Result
theDate

1984-03-07T12:31:14.645Z

1984-03-07T12:31:14.645876+01:00

1984-03-07T12:31:14.645876123+01:00[Europe/Stockholm]

1984-03-07T12:31:14+01:00[Europe/Stockholm]

1984-03-07T12:31:14Z

1984-03-07T12:00+01:00

1984-03-07T00:00+01:00[Europe/Stockholm]

7 rows

3.4. Creating a quarter (Year-Quarter-Day) DateTime

datetime() returns a DateTime value with the specified year, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The quarter component will default to 1 if quarter is omitted.

The day of the quarter component will default to 1 if dayOfQuarter is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, quarter, dayOfQuarter, hour, minute, and second may be omitted; i.e. it is possible to specify only year, quarter and dayOfQuarter, but specifying year, quarter, dayOfQuarter and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, microsecond: 645876}),
  datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, timezone: '+01:00'}),
  datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, quarter:3, dayOfQuarter: 45})
] as theDate
RETURN theDate
Table 23. Result
theDate

1984-08-14T12:31:14.645876Z

1984-08-14T12:31:14+01:00

1984-08-14T12:00+02:00[Europe/Stockholm]

1984-08-14T00:00Z

4 rows

3.5. Creating an ordinal (Year-Day) DateTime

datetime() returns a DateTime value with the specified year, ordinalDay, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, ordinalDay, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The ordinal day of the year component will default to 1 if ordinalDay is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, ordinalDay, hour, minute, and second may be omitted; i.e. it is possible to specify only year and ordinalDay, but specifying year, ordinalDay and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  datetime({year:1984, ordinalDay:202, hour:12, minute:31, second:14, millisecond: 645}),
  datetime({year:1984, ordinalDay:202, hour:12, minute:31, second:14, timezone: '+01:00'}),
  datetime({year:1984, ordinalDay:202, timezone: 'Europe/Stockholm'}),
  datetime({year:1984, ordinalDay:202})
] as theDate
RETURN theDate
Table 24. Result
theDate

1984-07-20T12:31:14.645Z

1984-07-20T12:31:14+01:00

1984-07-20T00:00+02:00[Europe/Stockholm]

1984-07-20T00:00Z

4 rows

3.6. Creating a DateTime from a string

datetime() returns the DateTime value obtained by parsing a string representation of a temporal value.

Syntax: datetime(temporalValue)

Returns:

A DateTime.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for dates, times and time zones.

The timezone component will default to the configured default time zone if it is omitted.

temporalValue must denote a valid date and time; i.e. a temporalValue denoting 30 February 2001 is invalid.

datetime(null) returns null.

Query
UNWIND [
  datetime('2015-07-21T21:40:32.142+0100'),
  datetime('2015-W30-2T214032.142Z'),
  datetime('2015T214032-0100'),
  datetime('20150721T21:40-01:30'),
  datetime('2015-W30T2140-02'),
  datetime('2015202T21+18:00'),
  datetime('2015-07-21T21:40:32.142[Europe/London]'),
  datetime('2015-07-21T21:40:32.142-04[America/New_York]')
] AS theDate
RETURN theDate
Table 25. Result
theDate

2015-07-21T21:40:32.142+01:00

2015-07-21T21:40:32.142Z

2015-01-01T21:40:32-01:00

2015-07-21T21:40-01:30

2015-07-20T21:40-02:00

2015-07-21T21:00+18:00

2015-07-21T21:40:32.142+01:00[Europe/London]

2015-07-21T21:40:32.142-04:00[America/New_York]

8 rows

3.7. Creating a DateTime using other temporal values as components

datetime() returns the DateTime value obtained by selecting and composing components from another temporal value. In essence, this allows a Date, LocalDateTime, Time or LocalTime value to be converted to a DateTime, and for "missing" components to be provided.

Syntax: datetime({datetime [, year, …​, timezone]}) | datetime({date [, year, …​, timezone]}) | datetime({time [, year, …​, timezone]}) | datetime({date, time [, year, …​, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

datetime

A DateTime value.

date

A Date value.

time

A Time value.

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of datetime, date and/or time.

datetime(dd) may be written instead of datetime({datetime: dd}).

Selecting a Time or DateTime value as the time component also selects its time zone. If a LocalTime or LocalDateTime is selected instead, the default time zone is used. In any case, the time zone can be overridden explicitly.

Selecting a DateTime as the datetime component and overwriting the time zone will adjust the local time to keep the same point in time.

Selecting a DateTime or Time as the time component and overwriting the time zone will adjust the local time to keep the same point in time.

The following query shows the various usages of datetime({date [, year, …​, timezone]})

Query
WITH date({year:1984, month:10, day:11}) AS dd
RETURN datetime({date:dd, hour: 10, minute: 10, second: 10}) AS dateHHMMSS,
       datetime({date:dd, hour: 10, minute: 10, second: 10, timezone:'+05:00'}) AS dateHHMMSSTimezone,
       datetime({date:dd, day: 28, hour: 10, minute: 10, second: 10}) AS dateDDHHMMSS,
       datetime({date:dd, day: 28, hour: 10, minute: 10, second: 10, timezone:'Pacific/Honolulu'}) AS dateDDHHMMSSTimezone
Table 26. Result
dateHHMMSS dateHHMMSSTimezone dateDDHHMMSS dateDDHHMMSSTimezone

1984-10-11T10:10:10Z

1984-10-11T10:10:10+05:00

1984-10-28T10:10:10Z

1984-10-28T10:10:10-10:00[Pacific/Honolulu]

1 row

The following query shows the various usages of datetime({time [, year, …​, timezone]})

Query
WITH time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}) AS tt
RETURN datetime({year:1984, month:10, day:11, time:tt}) AS YYYYMMDDTime,
       datetime({year:1984, month:10, day:11, time:tt, timezone:'+05:00'}) AS YYYYMMDDTimeTimezone,
       datetime({year:1984, month:10, day:11, time:tt, second: 42}) AS YYYYMMDDTimeSS,
       datetime({year:1984, month:10, day:11, time:tt, second: 42, timezone:'Pacific/Honolulu'}) AS YYYYMMDDTimeSSTimezone
Table 27. Result
YYYYMMDDTime YYYYMMDDTimeTimezone YYYYMMDDTimeSS YYYYMMDDTimeSSTimezone

1984-10-11T12:31:14.645876+01:00

1984-10-11T16:31:14.645876+05:00

1984-10-11T12:31:42.645876+01:00

1984-10-11T01:31:42.645876-10:00[Pacific/Honolulu]

1 row

The following query shows the various usages of datetime({date, time [, year, …​, timezone]}); i.e. combining a Date and a Time value to create a single DateTime value:

Query
WITH date({year:1984, month:10, day:11}) AS dd,
     localtime({hour:12, minute:31, second:14, millisecond: 645}) AS tt
RETURN datetime({date:dd, time:tt}) as dateTime,
      datetime({date:dd, time:tt, timezone:'+05:00'}) AS dateTimeTimezone,
      datetime({date:dd, time:tt, day: 28, second: 42}) AS dateTimeDDSS,
      datetime({date:dd, time:tt, day: 28, second: 42, timezone:'Pacific/Honolulu'}) AS dateTimeDDSSTimezone
Table 28. Result
dateTime dateTimeTimezone dateTimeDDSS dateTimeDDSSTimezone

1984-10-11T12:31:14.645Z

1984-10-11T12:31:14.645+05:00

1984-10-28T12:31:42.645Z

1984-10-28T12:31:42.645-10:00[Pacific/Honolulu]

1 row

The following query shows the various usages of datetime({datetime [, year, …​, timezone]})

Query
WITH datetime({year:1984, month:10, day:11, hour:12, timezone: 'Europe/Stockholm'}) AS dd
RETURN datetime({datetime:dd}) AS dateTime,
       datetime({datetime:dd, timezone:'+05:00'}) AS dateTimeTimezone,
       datetime({datetime:dd, day: 28, second: 42}) AS dateTimeDDSS,
       datetime({datetime:dd, day: 28, second: 42, timezone:'Pacific/Honolulu'}) AS dateTimeDDSSTimezone
Table 29. Result
dateTime dateTimeTimezone dateTimeDDSS dateTimeDDSSTimezone

1984-10-11T12:00+01:00[Europe/Stockholm]

1984-10-11T16:00+05:00

1984-10-28T12:00:42+01:00[Europe/Stockholm]

1984-10-28T01:00:42-10:00[Pacific/Honolulu]

1 row

3.8. Creating a DateTime from a timestamp

datetime() returns the DateTime value at the specified number of seconds or milliseconds from the UNIX epoch in the UTC time zone.

Conversions to other temporal instant types from UNIX epoch representations can be achieved by transforming a DateTime value to one of these types.

Syntax: datetime({ epochSeconds | epochMillis })

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

epochSeconds

A numeric value representing the number of seconds from the UNIX epoch in the UTC time zone.

epochMillis

A numeric value representing the number of milliseconds from the UNIX epoch in the UTC time zone.

Considerations:

epochSeconds/epochMillis may be used in conjunction with nanosecond

Query
RETURN datetime({ epochSeconds:timestamp()/ 1000, nanosecond: 23 }) AS theDate
Table 30. Result
theDate

2020-12-02T16:03:10.000000023Z

1 row

Query
RETURN datetime({ epochMillis: 424797300000 }) AS theDate
Table 31. Result
theDate

1983-06-18T15:15Z

1 row

3.9. Truncating a DateTime

datetime.truncate() returns the DateTime value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the DateTime returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, day — with some value x — may be provided when the truncation unit is year in order to ensure the returned value has the day set to x instead of the default day (which is 1).

Syntax: datetime.truncate(unit [, temporalInstantValue [, mapOfComponents ] ])

Returns:

A DateTime.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {millennium, century, decade, year, weekYear, quarter, month, week, day, hour, minute, second, millisecond, microsecond}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Date}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit. During truncation, a time zone can be attached or overridden using the key timezone.

Considerations:

temporalInstantValue cannot be a Date value if unit is one of {hour, minute, second, millisecond, microsecond}.

The time zone of temporalInstantValue may be overridden; for example, datetime.truncate('minute', input, {timezone:'+0200'}).

If temporalInstantValue is one of {Time, DateTime} — a value with a time zone — and the time zone is overridden, no time conversion occurs.

If temporalInstantValue is one of {LocalDateTime, Date} — a value without a time zone — and the time zone is not overridden, the configured default time zone will be used.

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'day', mapOfComponents cannot contain information pertaining to a month.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

If temporalInstantValue is not provided, it will be set to the current date, time and timezone, i.e. datetime.truncate(unit) is equivalent of datetime.truncate(unit, datetime()).

Query
WITH datetime({year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+03:00'}) AS d
RETURN datetime.truncate('millennium', d, {timezone:'Europe/Stockholm'}) AS truncMillenium,
       datetime.truncate('year', d, {day:5}) AS truncYear,
       datetime.truncate('month', d) AS truncMonth,
       datetime.truncate('day', d, {millisecond:2}) AS truncDay,
       datetime.truncate('hour', d) AS truncHour,
       datetime.truncate('second', d) AS truncSecond
Table 32. Result
truncMillenium truncYear truncMonth truncDay truncHour truncSecond

2000-01-01T00:00+01:00[Europe/Stockholm]

2017-01-05T00:00+03:00

2017-11-01T00:00+03:00

2017-11-11T00:00:00.002+03:00

2017-11-11T12:00+03:00

2017-11-11T12:31:14+03:00

1 row

4. LocalDateTime: localdatetime()

Details for using the localdatetime() function.

4.1. Getting the current LocalDateTime

localdatetime() returns the current LocalDateTime value. If no time zone parameter is specified, the local time zone will be used.

Syntax: localdatetime([ +{timezone}+ ])

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, localdatetime() must be invoked (localdatetime({}) is invalid).

Query
RETURN localdatetime() AS now

The current local date and time (i.e. in the local time zone) is returned.

Table 33. Result
now

2020-12-02T16:03:10.235

1 row

Query
RETURN localdatetime({ timezone: 'America/Los Angeles' }) AS now

The current local date and time in California is returned.

Table 34. Result
now

2020-12-02T08:03:10.249

1 row

4.1.1. localdatetime.transaction()

localdatetime.transaction() returns the current LocalDateTime value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: localdatetime.transaction([ +{timezone}+ ])

Returns:

A LocalDateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localdatetime.transaction() AS now
Table 35. Result
now

2020-12-02T16:03:10.264

1 row

4.1.2. localdatetime.statement()

localdatetime.statement() returns the current LocalDateTime value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: localdatetime.statement([ +{timezone}+ ])

Returns:

A LocalDateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localdatetime.statement() AS now
Table 36. Result
now

2020-12-02T16:03:10.278

1 row

4.1.3. localdatetime.realtime()

localdatetime.realtime() returns the current LocalDateTime value using the realtime clock. This value will be the live clock of the system.

Syntax: localdatetime.realtime([ +{timezone}+ ])

Returns:

A LocalDateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localdatetime.realtime() AS now
Table 37. Result
now

2020-12-02T16:03:10.314662

1 row

Query
RETURN localdatetime.realtime('America/Los Angeles') AS nowInLA
Table 38. Result
nowInLA

2020-12-02T08:03:10.329037

1 row

4.2. Creating a calendar (Year-Month-Day) LocalDateTime

localdatetime() returns a LocalDateTime value with the specified year, month, day, hour, minute, second, millisecond, microsecond and nanosecond component values.

Syntax: localdatetime({year [, month, day, hour, minute, second, millisecond, microsecond, nanosecond]})

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

The month component will default to 1 if month is omitted.

The day of the month component will default to 1 if day is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, month, day, hour, minute, and second may be omitted; i.e. it is possible to specify only year, month and day, but specifying year, month, day and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
RETURN localdatetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789 }) AS theDate
Table 39. Result
theDate

1984-10-11T12:31:14.123456789

1 row

4.3. Creating a week (Year-Week-Day) LocalDateTime

localdatetime() returns a LocalDateTime value with the specified year, week, dayOfWeek, hour, minute, second, millisecond, microsecond and nanosecond component values.

Syntax: localdatetime({year [, week, dayOfWeek, hour, minute, second, millisecond, microsecond, nanosecond]})

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

The week component will default to 1 if week is omitted.

The day of the week component will default to 1 if dayOfWeek is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, week, dayOfWeek, hour, minute, and second may be omitted; i.e. it is possible to specify only year, week and dayOfWeek, but specifying year, week, dayOfWeek and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
RETURN localdatetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, millisecond: 645 }) AS theDate
Table 40. Result
theDate

1984-03-07T12:31:14.645

1 row

4.4. Creating a quarter (Year-Quarter-Day) DateTime

localdatetime() returns a LocalDateTime value with the specified year, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond and nanosecond component values.

Syntax: localdatetime({year [, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond, nanosecond]})

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

The quarter component will default to 1 if quarter is omitted.

The day of the quarter component will default to 1 if dayOfQuarter is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, quarter, dayOfQuarter, hour, minute, and second may be omitted; i.e. it is possible to specify only year, quarter and dayOfQuarter, but specifying year, quarter, dayOfQuarter and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
RETURN localdatetime({ year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, nanosecond: 645876123 }) AS theDate
Table 41. Result
theDate

1984-08-14T12:31:14.645876123

1 row

4.5. Creating an ordinal (Year-Day) LocalDateTime

localdatetime() returns a LocalDateTime value with the specified year, ordinalDay, hour, minute, second, millisecond, microsecond and nanosecond component values.

Syntax: localdatetime({year [, ordinalDay, hour, minute, second, millisecond, microsecond, nanosecond]})

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

year

An expression consisting of at least four digits that specifies the year.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

The ordinal day of the year component will default to 1 if ordinalDay is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, ordinalDay, hour, minute, and second may be omitted; i.e. it is possible to specify only year and ordinalDay, but specifying year, ordinalDay and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
RETURN localdatetime({ year:1984, ordinalDay:202, hour:12, minute:31, second:14, microsecond: 645876 }) AS theDate
Table 42. Result
theDate

1984-07-20T12:31:14.645876

1 row

4.6. Creating a LocalDateTime from a string

localdatetime() returns the LocalDateTime value obtained by parsing a string representation of a temporal value.

Syntax: localdatetime(temporalValue)

Returns:

A LocalDateTime.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for dates and times.

temporalValue must denote a valid date and time; i.e. a temporalValue denoting 30 February 2001 is invalid.

localdatetime(null) returns null.

Query
UNWIND [
  localdatetime('2015-07-21T21:40:32.142'),
  localdatetime('2015-W30-2T214032.142'),
  localdatetime('2015-202T21:40:32'),
  localdatetime('2015202T21')
] AS theDate
RETURN theDate
Table 43. Result
theDate

2015-07-21T21:40:32.142

2015-07-21T21:40:32.142

2015-07-21T21:40:32

2015-07-21T21:00

4 rows

4.7. Creating a LocalDateTime using other temporal values as components

localdatetime() returns the LocalDateTime value obtained by selecting and composing components from another temporal value. In essence, this allows a Date, DateTime, Time or LocalTime value to be converted to a LocalDateTime, and for "missing" components to be provided.

Syntax: localdatetime({datetime [, year, …​, nanosecond]}) | localdatetime({date [, year, …​, nanosecond]}) | localdatetime({time [, year, …​, nanosecond]}) | localdatetime({date, time [, year, …​, nanosecond]})

Returns:

A LocalDateTime.

Arguments:

Name Description

A single map consisting of the following:

datetime

A DateTime value.

date

A Date value.

time

A Time value.

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of datetime, date and/or time.

localdatetime(dd) may be written instead of localdatetime({datetime: dd}).

The following query shows the various usages of localdatetime({date [, year, …​, nanosecond]})

Query
WITH date({year:1984, month:10, day:11}) AS dd
RETURN localdatetime({date:dd, hour: 10, minute: 10, second: 10}) AS dateHHMMSS,
       localdatetime({date:dd, day: 28, hour: 10, minute: 10, second: 10}) AS dateDDHHMMSS
Table 44. Result
dateHHMMSS dateDDHHMMSS

1984-10-11T10:10:10

1984-10-28T10:10:10

1 row

The following query shows the various usages of localdatetime({time [, year, …​, nanosecond]})

Query
WITH time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}) AS tt
RETURN localdatetime({year:1984, month:10, day:11, time:tt}) AS YYYYMMDDTime,
       localdatetime({year:1984, month:10, day:11, time:tt, second: 42}) AS YYYYMMDDTimeSS
Table 45. Result
YYYYMMDDTime YYYYMMDDTimeSS

1984-10-11T12:31:14.645876

1984-10-11T12:31:42.645876

1 row

The following query shows the various usages of localdatetime({date, time [, year, …​, nanosecond]}); i.e. combining a Date and a Time value to create a single LocalDateTime value:

Query
WITH date({year:1984, month:10, day:11}) AS dd,
     time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}) AS tt
RETURN localdatetime({date:dd, time:tt}) AS dateTime,
       localdatetime({date:dd, time:tt, day: 28, second: 42}) AS dateTimeDDSS
Table 46. Result
dateTime dateTimeDDSS

1984-10-11T12:31:14.645876

1984-10-28T12:31:42.645876

1 row

The following query shows the various usages of localdatetime({datetime [, year, …​, nanosecond]})

Query
WITH datetime({year:1984, month:10, day:11, hour:12, timezone: '+01:00'}) as dd
RETURN localdatetime({datetime:dd}) as dateTime,
       localdatetime({datetime:dd, day: 28, second: 42}) as dateTimeDDSS
Table 47. Result
dateTime dateTimeDDSS

1984-10-11T12:00

1984-10-28T12:00:42

1 row

4.8. Truncating a LocalDateTime

localdatetime.truncate() returns the LocalDateTime value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the LocalDateTime returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, day — with some value x — may be provided when the truncation unit is year in order to ensure the returned value has the day set to x instead of the default day (which is 1).

Syntax: localdatetime.truncate(unit [, temporalInstantValue [, mapOfComponents ] ])

Returns:

A LocalDateTime.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {millennium, century, decade, year, weekYear, quarter, month, week, day, hour, minute, second, millisecond, microsecond}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Date}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit.

Considerations:

temporalInstantValue cannot be a Date value if unit is one of {hour, minute, second, millisecond, microsecond}.

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'day', mapOfComponents cannot contain information pertaining to a month.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

If temporalInstantValue is not provided, it will be set to the current date and time, i.e. localdatetime.truncate(unit) is equivalent of localdatetime.truncate(unit, localdatetime()).

Query
WITH localdatetime({year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123}) AS d
RETURN localdatetime.truncate('millennium', d) AS truncMillenium,
       localdatetime.truncate('year', d, {day:2}) AS truncYear,
       localdatetime.truncate('month', d) AS truncMonth,
       localdatetime.truncate('day', d) AS truncDay,
       localdatetime.truncate('hour', d, {nanosecond:2}) AS truncHour,
       localdatetime.truncate('second', d) AS truncSecond
Table 48. Result
truncMillenium truncYear truncMonth truncDay truncHour truncSecond

2000-01-01T00:00

2017-01-02T00:00

2017-11-01T00:00

2017-11-11T00:00

2017-11-11T12:00:00.000000002

2017-11-11T12:31:14

1 row

5. LocalTime: localtime()

Details for using the localtime() function.

5.1. Getting the current LocalTime

localtime() returns the current LocalTime value. If no time zone parameter is specified, the local time zone will be used.

Syntax: localtime([ +{timezone}+ ])

Returns:

A LocalTime.

Arguments:

Name Description

A single map consisting of the following:

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, localtime() must be invoked (localtime({}) is invalid).

Query
RETURN localtime() AS now

The current local time (i.e. in the local time zone) is returned.

Table 49. Result
now

16:03:10.623

1 row

Query
RETURN localtime({ timezone: 'America/Los Angeles' }) AS nowInLA

The current local time in California is returned.

Table 50. Result
nowInLA

08:03:10.635

1 row

5.1.1. localtime.transaction()

localtime.transaction() returns the current LocalTime value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: localtime.transaction([ +{timezone}+ ])

Returns:

A LocalTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localtime.transaction() AS now
Table 51. Result
now

16:03:10.648

1 row

5.1.2. localtime.statement()

localtime.statement() returns the current LocalTime value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: localtime.statement([ +{timezone}+ ])

Returns:

A LocalTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localtime.statement() AS now
Table 52. Result
now

16:03:10.667

1 row

Query
RETURN localtime.statement('America/Los Angeles') AS nowInLA
Table 53. Result
nowInLA

08:03:10.678

1 row

5.1.3. localtime.realtime()

localtime.realtime() returns the current LocalTime value using the realtime clock. This value will be the live clock of the system.

Syntax: localtime.realtime([ +{timezone}+ ])

Returns:

A LocalTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN localtime.realtime() AS now
Table 54. Result
now

16:03:10.701523

1 row

5.2. Creating a LocalTime

localtime() returns a LocalTime value with the specified hour, minute, second, millisecond, microsecond and nanosecond component values.

Syntax: localtime({hour [, minute, second, millisecond, microsecond, nanosecond]})

Returns:

A LocalTime.

Arguments:

Name Description

A single map consisting of the following:

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set hour, minute, and second may be omitted; i.e. it is possible to specify only hour and minute, but specifying hour and second is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  localtime({hour:12, minute:31, second:14, nanosecond: 789, millisecond: 123, microsecond: 456}),
  localtime({hour:12, minute:31, second:14}),
  localtime({hour:12})
] as theTime
RETURN theTime
Table 55. Result
theTime

12:31:14.123456789

12:31:14

12:00

3 rows

5.3. Creating a LocalTime from a string

localtime() returns the LocalTime value obtained by parsing a string representation of a temporal value.

Syntax: localtime(temporalValue)

Returns:

A LocalTime.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for times.

temporalValue must denote a valid time; i.e. a temporalValue denoting 13:46:64 is invalid.

localtime(null) returns null.

Query
UNWIND [
  localtime('21:40:32.142'),
  localtime('214032.142'),
  localtime('21:40'),
  localtime('21')
] AS theTime
RETURN theTime
Table 56. Result
theTime

21:40:32.142

21:40:32.142

21:40

21:00

4 rows

5.4. Creating a LocalTime using other temporal values as components

localtime() returns the LocalTime value obtained by selecting and composing components from another temporal value. In essence, this allows a DateTime, LocalDateTime or Time value to be converted to a LocalTime, and for "missing" components to be provided.

Syntax: localtime({time [, hour, …​, nanosecond]})

Returns:

A LocalTime.

Arguments:

Name Description

A single map consisting of the following:

time

A Time value.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of time.

localtime(tt) may be written instead of localtime({time: tt}).

Query
WITH time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}) AS tt
RETURN localtime({time:tt}) AS timeOnly,
       localtime({time:tt, second: 42}) AS timeSS
Table 57. Result
timeOnly timeSS

12:31:14.645876

12:31:42.645876

1 row

5.5. Truncating a LocalTime

localtime.truncate() returns the LocalTime value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the LocalTime returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, minute — with some value x — may be provided when the truncation unit is hour in order to ensure the returned value has the minute set to x instead of the default minute (which is 1).

Syntax: localtime.truncate(unit [, temporalInstantValue [, mapOfComponents ] ])

Returns:

A LocalTime.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {day, hour, minute, second, millisecond, microsecond}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Time, LocalTime}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit.

Considerations:

Truncating time to day — i.e. unit is 'day'  — is supported, and yields midnight at the start of the day (00:00), regardless of the value of temporalInstantValue. However, the time zone of temporalInstantValue is retained.

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'second', mapOfComponents cannot contain information pertaining to a minute.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

If temporalInstantValue is not provided, it will be set to the current time, i.e. localtime.truncate(unit) is equivalent of localtime.truncate(unit, localtime()).

Query
WITH time({hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '-01:00'}) AS t
RETURN localtime.truncate('day', t) AS truncDay,
       localtime.truncate('hour', t) AS truncHour,
       localtime.truncate('minute', t, {millisecond:2}) AS truncMinute,
       localtime.truncate('second', t) AS truncSecond,
       localtime.truncate('millisecond', t) AS truncMillisecond,
       localtime.truncate('microsecond', t) AS truncMicrosecond
Table 58. Result
truncDay truncHour truncMinute truncSecond truncMillisecond truncMicrosecond

00:00

12:00

12:31:00.002

12:31:14

12:31:14.645

12:31:14.645876

1 row

6. Time: time()

Details for using the time() function.

6.1. Getting the current Time

time() returns the current Time value. If no time zone parameter is specified, the local time zone will be used.

Syntax: time([ +{timezone}+ ])

Returns:

A Time.

Arguments:

Name Description

A single map consisting of the following:

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, time() must be invoked (time({}) is invalid).

Query
RETURN time() AS currentTime

The current time of day using the local time zone is returned.

Table 59. Result
currentTime

16:03:10.842Z

1 row

Query
RETURN time({ timezone: 'America/Los Angeles' }) AS currentTimeInLA

The current time of day in California is returned.

Table 60. Result
currentTimeInLA

08:03:10.855-08:00

1 row

6.1.1. time.transaction()

time.transaction() returns the current Time value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: time.transaction([ +{timezone}+ ])

Returns:

A Time.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN time.transaction() AS currentTime
Table 61. Result
currentTime

16:03:10.870Z

1 row

6.1.2. time.statement()

time.statement() returns the current Time value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: time.statement([ +{timezone}+ ])

Returns:

A Time.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN time.statement() AS currentTime
Table 62. Result
currentTime

16:03:10.884Z

1 row

Query
RETURN time.statement('America/Los Angeles') AS currentTimeInLA
Table 63. Result
currentTimeInLA

08:03:10.897-08:00

1 row

6.1.3. time.realtime()

time.realtime() returns the current Time value using the realtime clock. This value will be the live clock of the system.

Syntax: time.realtime([ +{timezone}+ ])

Returns:

A Time.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query
RETURN time.realtime() AS currentTime
Table 64. Result
currentTime

16:03:10.923886Z

1 row

6.2. Creating a Time

time() returns a Time value with the specified hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: time({hour [, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A Time.

Arguments:

Name Description

A single map consisting of the following:

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set hour, minute, and second may be omitted; i.e. it is possible to specify only hour and minute, but specifying hour and second is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query
UNWIND [
  time({hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789}),
  time({hour:12, minute:31, second:14, nanosecond: 645876123}),
  time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}),
  time({hour:12, minute:31, timezone: '+01:00'}),
  time({hour:12, timezone: '+01:00'})
] AS theTime
RETURN theTime
Table 65. Result
theTime

12:31:14.123456789Z

12:31:14.645876123Z

12:31:14.645876+01:00

12:31+01:00

12:00+01:00

5 rows

6.3. Creating a Time from a string

time() returns the Time value obtained by parsing a string representation of a temporal value.

Syntax: time(temporalValue)

Returns:

A Time.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for times and time zones.

The timezone component will default to the configured default time zone if it is omitted.

temporalValue must denote a valid time; i.e. a temporalValue denoting 15:67 is invalid.

time(null) returns null.

Query
UNWIND [
  time('21:40:32.142+0100'),
  time('214032.142Z'),
  time('21:40:32+01:00'),
  time('214032-0100'),
  time('21:40-01:30'),
  time('2140-00:00'),
  time('2140-02'),
  time('22+18:00')
] AS theTime
RETURN theTime
Table 66. Result
theTime

21:40:32.142+01:00

21:40:32.142Z

21:40:32+01:00

21:40:32-01:00

21:40-01:30

21:40Z

21:40-02:00

22:00+18:00

8 rows

6.4. Creating a Time using other temporal values as components

time() returns the Time value obtained by selecting and composing components from another temporal value. In essence, this allows a DateTime, LocalDateTime or LocalTime value to be converted to a Time, and for "missing" components to be provided.

Syntax: time({time [, hour, …​, timezone]})

Returns:

A Time.

Arguments:

Name Description

A single map consisting of the following:

time

A Time value.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of time.

time(tt) may be written instead of time({time: tt}).

Selecting a Time or DateTime value as the time component also selects its time zone. If a LocalTime or LocalDateTime is selected instead, the default time zone is used. In any case, the time zone can be overridden explicitly.

Selecting a DateTime or Time as the time component and overwriting the time zone will adjust the local time to keep the same point in time.

Query
WITH localtime({hour:12, minute:31, second:14, microsecond: 645876}) AS tt
RETURN time({time:tt}) AS timeOnly,
       time({time:tt, timezone:'+05:00'}) AS timeTimezone,
       time({time:tt, second: 42}) AS timeSS,
       time({time:tt, second: 42, timezone:'+05:00'}) AS timeSSTimezone
Table 67. Result
timeOnly timeTimezone timeSS timeSSTimezone

12:31:14.645876Z

12:31:14.645876+05:00

12:31:42.645876Z

12:31:42.645876+05:00

1 row

6.5. Truncating a Time

time.truncate() returns the Time value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the Time returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, minute — with some value x — may be provided when the truncation unit is hour in order to ensure the returned value has the minute set to x instead of the default minute (which is 1).

Syntax: time.truncate(unit [, temporalInstantValue [, mapOfComponents ] ])

Returns:

A Time.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {day, hour, minute, second, millisecond, microsecond}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Time, LocalTime}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit. During truncation, a time zone can be attached or overridden using the key timezone.

Considerations:

Truncating time to day — i.e. unit is 'day'  — is supported, and yields midnight at the start of the day (00:00), regardless of the value of temporalInstantValue. However, the time zone of temporalInstantValue is retained.

The time zone of temporalInstantValue may be overridden; for example, time.truncate('minute', input, {timezone:'+0200'}).

If temporalInstantValue is one of {Time, DateTime} — a value with a time zone — and the time zone is overridden, no time conversion occurs.

If temporalInstantValue is one of {LocalTime, LocalDateTime, Date} — a value without a time zone — and the time zone is not overridden, the configured default time zone will be used.

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'second', mapOfComponents cannot contain information pertaining to a minute.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

If temporalInstantValue is not provided, it will be set to the current time and timezone, i.e. time.truncate(unit) is equivalent of time.truncate(unit, time()).

Query
WITH time({ hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '-01:00' }) AS t
RETURN time.truncate('day', t) AS truncDay, time.truncate('hour', t) AS truncHour, time.truncate('minute', t) AS truncMinute, time.truncate('second', t) AS truncSecond, time.truncate('millisecond', t, { nanosecond:2 }) AS truncMillisecond, time.truncate('microsecond', t) AS truncMicrosecond
Table 68. Result
truncDay truncHour truncMinute truncSecond truncMillisecond truncMicrosecond

00:00-01:00

12:00-01:00

12:31-01:00

12:31:14-01:00

12:31:14.645000002-01:00

12:31:14.645876-01:00

1 row