Temporal functions - instant types

Temporal functions allow for the creation and manipulation of values for each temporal type — DATE, ZONED TIME, LOCAL TIME, ZONED DATETIME, and LOCAL DATETIME.

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

Temporal instant types

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 ZONED TIME to create a ZONED DATETIME), or

    • selecting parts from a temporal value (such as selecting the DATE from a ZONED 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 ZONED 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, LOCAL TIME and ZONED 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.

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()

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)

date()

Details for using the date() function.

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:

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).
Example 1. date()
Query
RETURN date() AS currentDate

The current date is returned.

Table 2. Result
currentDate

2022-06-14

Rows: 1
Example 2. date()
Query
RETURN date({timezone: 'America/Los Angeles'}) AS currentDateInLA

The current date in California is returned.

Table 3. Result
currentDateInLA

2022-06-14

Rows: 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:

DATE

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 3. date.transaction()
Query
RETURN date.transaction() AS currentDate
Table 4. Result
currentDate

2022-06-14

Rows: 1

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:

DATE

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 4. date.statement()
Query
RETURN date.statement() AS currentDate
Table 5. Result
currentDate

2022-06-14

Rows: 1

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:

DATE

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 5. date.realtime()
Query
RETURN date.realtime() AS currentDate
Table 6. Result
currentDate

2022-06-14

Rows: 1
Example 6. date.realtime()
Query
RETURN date.realtime('America/Los Angeles') AS currentDateInLA
Table 7. Result
currentDateInLA

2022-06-14

Rows: 1

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:

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.
Example 7. date()
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

Rows: 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:

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.
Example 8. date()
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

Rows: 3

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:

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.
Example 9. date()
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

Rows: 3

Creating an ordinal (Year-Day) DATE

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

Syntax:

date({year [, ordinalDay]})

Returns:

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.
Example 10. date()
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

Rows: 2

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:

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.
Example 11. date()
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

Rows: 6

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 ZONED DATETIME or LOCAL DATETIME 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:

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}).
Example 12. date()
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

Rows: 3

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 string 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:

DATE

Arguments:

Name Description

unit

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

temporalInstantValue

An expression of one of the following types: ZONED DATETIME, LOCAL DATETIME, 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 string 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()).
Example 13. date.truncate()
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

Rows: 1

datetime()

Details for using the datetime() function.

Getting the current ZONED DATETIME

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

Syntax:

datetime([{timezone}])

Returns:

ZONED 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).
Example 14. .datetime()
Query
RETURN datetime() AS currentDateTime

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

Table 15. Result
currentDateTime

2022-06-14T10:02:28.192Z

Rows: 1
Example 15. .datetime()
Query
RETURN datetime({timezone: 'America/Los Angeles'}) AS currentDateTimeInLA

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

Table 16. Result
currentDateTimeInLA

2022-06-14T03:02:28.238-07:00[America/Los_Angeles]

Rows: 1

datetime.transaction()

datetime.transaction() returns the current ZONED 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:

ZONED DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 16. datetime.transaction()
Query
RETURN datetime.transaction() AS currentDateTime
Table 17. Result
currentDateTime

2022-06-14T10:02:28.290Z

Rows: 1
Example 17. datetime.transaction()
Query
RETURN datetime.transaction('America/Los Angeles') AS currentDateTimeInLA
Table 18. Result
currentDateTimeInLA

2022-06-14T03:02:28.338-07:00[America/Los_Angeles]

Rows: 1

datetime.statement()

datetime.statement() returns the current ZONED 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:

ZONED DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 18. datetime.statement()
Query
RETURN datetime.statement() AS currentDateTime
Table 19. Result
currentDateTime

2022-06-14T10:02:28.395Z

Rows: 1

datetime.realtime()

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

Syntax:

datetime.realtime([{timezone}])

Returns:

ZONED DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 19. datetime.realtime()
Query
RETURN datetime.realtime() AS currentDateTime
Table 20. Result
currentDateTime

2022-06-14T10:02:28.494444Z

Rows: 1

Creating a calendar (Year-Month-Day) ZONED DATETIME

datetime() returns a ZONED 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:

ZONED 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.
Example 20. datetime()
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]

Rows: 8

Creating a week (Year-Week-Day) ZONED DATETIME

datetime() returns a ZONED 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:

ZONED 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.
Example 21. datetime()
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]

Rows: 7

Creating a quarter (Year-Quarter-Day) ZONED DATETIME

datetime() returns a ZONED 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:

ZONED 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.
Example 22. datetime()
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

Rows: 4

Creating an ordinal (Year-Day) ZONED DATETIME

datetime() returns a ZONED 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:

ZONED 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.
Example 23. datetime()
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

Rows: 4

Creating a ZONED DATETIME from a STRING

datetime() returns the ZONED DATETIME value obtained by parsing a STRING representation of a temporal value.

Syntax:

datetime(temporalValue)

Returns:

ZONED 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.
Example 24. datetime()
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]

Rows: 8

Creating a ZONED DATETIME using other temporal values as components

datetime() returns the ZONED DATETIME value obtained by selecting and composing components from another temporal value. In essence, this allows a DATE, LOCAL DATETIME, ZONED TIME or LOCAL TIME value to be converted to a ZONED 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:

ZONED DATETIME

Arguments:

Name Description

A single map consisting of the following:

datetime

A ZONED DATETIME value.

date

A DATE value.

time

A ZONED 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 ZONED TIME or ZONED DATETIME value as the time component also selects its time zone. If a LOCAL TIME or LOCAL DATETIME is selected instead, the default time zone is used. In any case, the time zone can be overridden explicitly.

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

Selecting a ZONED DATETIME or ZONED TIME as the time component and overwriting the time zone will adjust the local time to keep the same point in time.
Example 25. datetime()

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]

Rows: 1
Example 26. datetime()

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]

Rows: 1
Example 27. datetime()

The following query shows the various usages of datetime({date, time [, year, ..., timezone]}); i.e. combining a DATE and a ZONED TIME value to create a single ZONED 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]

Rows: 1
Example 28. datetime()

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]

Rows: 1

Creating a ZONED DATETIME from a timestamp

datetime() returns the ZONED 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 ZONED DATETIME value to one of these types.

Syntax:

datetime({ epochSeconds | epochMillis })

Returns:

ZONED 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.
Example 29. datetime()
Query
RETURN datetime({epochSeconds: timestamp() / 1000, nanosecond: 23}) AS theDate
Table 30. Result
theDate

2022-06-14T10:02:30.000000023Z

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

1983-06-18T15:15Z

Rows: 1

Truncating a ZONED DATETIME

datetime.truncate() returns the ZONED 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 ZONED 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 string 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:

ZONED DATETIME

Arguments:

Name Description

unit

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

temporalInstantValue

An expression of one of the following types: ZONED DATETIME, LOCAL DATETIME, 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 ZONED TIME, ZONED DATETIME — a value with a time zone — and the time zone is overridden, no time conversion occurs.

If temporalInstantValue is one of LOCAL DATETIME, 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()).
Example 31. 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

Rows: 1

localdatetime()

Details for using the localdatetime() function.

Getting the current LOCAL DATETIME

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

Syntax:

localdatetime([{timezone}])

Returns:

LOCAL 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, localdatetime() must be invoked (localdatetime({}) is invalid).
Example 32. localdatetime()
Query
RETURN localdatetime() AS now

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

Table 33. Result
now

2022-06-14T10:02:30.447

Rows: 1
Example 33. localdatetime()
Query
RETURN localdatetime({timezone: 'America/Los Angeles'}) AS now

The current local date and time in California is returned.

Table 34. Result
now

2022-06-14T03:02:30.482

Rows: 1

localdatetime.transaction()

localdatetime.transaction() returns the current LOCAL 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:

localdatetime.transaction([{timezone}])

Returns:

LOCAL DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 34. localdatetime.transaction()
Query
RETURN localdatetime.transaction() AS now
Table 35. Result
now

2022-06-14T10:02:30.532

Rows: 1

localdatetime.statement()

localdatetime.statement() returns the current LOCAL 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:

localdatetime.statement([{timezone}])

Returns:

LOCAL DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 35. localdatetime.statement()
Query
RETURN localdatetime.statement() AS now
Table 36. Result
now

2022-06-14T10:02:30.570

Rows: 1

localdatetime.realtime()

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

Syntax:

localdatetime.realtime([{timezone}])

Returns:

LOCAL DATETIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 36. localdatetime.realtime()
Query
RETURN localdatetime.realtime() AS now
Table 37. Result
now

2022-06-14T10:02:30.647817

Rows: 1
Example 37. localdatetime.realtime()
Query
RETURN localdatetime.realtime('America/Los Angeles') AS nowInLA
Table 38. Result
nowInLA

2022-06-14T03:02:30.691099

Rows: 1

Creating a calendar (Year-Month-Day) LOCAL DATETIME

localdatetime() returns a LOCAL DATETIME 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:

LOCAL 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.

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.
Example 38. localdatetime.realtime()
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

Rows: 1

Creating a week (Year-Week-Day) LOCAL DATETIME

localdatetime() returns a LOCAL DATETIME 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:

LOCAL 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.

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.
Example 39. localdatetime()
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

Rows: 1

Creating a quarter (Year-Quarter-Day) ZONED DATETIME

localdatetime() returns a LOCAL DATETIME 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:

LOCAL 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.

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.
Example 40. localdatetime()
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

Rows: 1

Creating an ordinal (Year-Day) LOCAL DATETIME

localdatetime() returns a LOCAL DATETIME 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:

LOCAL 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.

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.
Example 41. localdatetime()
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

Rows: 1

Creating a LOCAL DATETIME from a STRING

localdatetime() returns the LOCAL DATETIME value obtained by parsing a STRING representation of a temporal value.

Syntax:

localdatetime(temporalValue)

Returns:

LOCAL DATETIME

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.
Example 42. localdatetime()
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

Rows: 4

Creating a LOCAL DATETIME using other temporal values as components

localdatetime() returns the LOCAL DATETIME value obtained by selecting and composing components from another temporal value. In essence, this allows a DATE, ZONED DATETIME, ZONED TIME or LOCAL TIME value to be converted to a LOCAL DATETIME, 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:

LOCAL DATETIME

Arguments:

Name Description

A single map consisting of the following:

datetime

A ZONED DATETIME value.

date

A DATE value.

time

A ZONED 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}).
Example 43. localdatetime()

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

Rows: 1
Example 44. localdatetime()

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

Rows: 1
Example 45. localdatetime()

The following query shows the various usages of localdatetime({date, time [, year, ..., nanosecond]}); i.e. combining a DATE and a ZONED TIME value to create a single LOCAL DATETIME 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

Rows: 1
Example 46. localdatetime()

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

Rows: 1

Truncating a LOCAL DATETIME

localdatetime.truncate() returns the LOCAL 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 LOCAL 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 string 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:

LOCAL DATETIME

Arguments:

Name Description

unit

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

temporalInstantValue

An expression of one of the following types: ZONED DATETIME, LOCAL DATETIME, 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()).
Example 47. localdatetime.truncate()
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

Rows: 1

localtime()

Details for using the localtime() function.

Getting the current LOCAL TIME

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

Syntax:

localtime([{timezone}])

Returns:

LOCAL 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, localtime() must be invoked (localtime({}) is invalid).
Example 48. localtime()
Query
RETURN localtime() AS now

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

Table 49. Result
now

10:02:31.596

Rows: 1
Example 49. localtime()
Query
RETURN localtime({timezone: 'America/Los Angeles'}) AS nowInLA

The current local time in California is returned.

Table 50. Result
nowInLA

03:02:31.629

Rows: 1

localtime.transaction()

localtime.transaction() returns the current LOCAL 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:

localtime.transaction([{timezone}])

Returns:

LOCAL TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 50. localtime.transaction()
Query
RETURN localtime.transaction() AS now
Table 51. Result
now

10:02:31.662

Rows: 1

localtime.statement()

localtime.statement() returns the current LOCAL 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:

localtime.statement([{timezone}])

Returns:

LOCAL TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 51. localtime.statement()
Query
RETURN localtime.statement() AS now
Table 52. Result
now

10:02:31.697

Rows: 1
Example 52. localtime.statement()
Query
RETURN localtime.statement('America/Los Angeles') AS nowInLA
Table 53. Result
nowInLA

03:02:31.737

Rows: 1

localtime.realtime()

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

Syntax:

localtime.realtime([{timezone}])

Returns:

LOCAL TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 53. localtime.realtime()
Query
RETURN localtime.realtime() AS now
Table 54. Result
now

10:02:31.806895

Rows: 1

Creating a LOCAL TIME

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

Syntax:

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

Returns:

LOCAL 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.

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.
Example 54. localtime()
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

Rows: 3

Creating a LOCAL TIME from a STRING

localtime() returns the LOCAL TIME value obtained by parsing a STRING representation of a temporal value.

Syntax:

localtime(temporalValue)

Returns:

LOCAL TIME

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.
Example 55. localtime()
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

Rows: 4

Creating a LOCAL TIME using other temporal values as components

localtime() returns the LOCAL TIME value obtained by selecting and composing components from another temporal value. In essence, this allows a ZONED DATETIME, LOCAL DATETIME or ZONED TIME value to be converted to a LOCAL TIME, and for "missing" components to be provided.

Syntax:

localtime({time [, hour, ..., nanosecond]})

Returns:

LOCAL TIME

Arguments:

Name Description

A single map consisting of the following:

time

A ZONED 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}).
Example 56. localtime()
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

Rows: 1

Truncating a LOCAL TIME

localtime.truncate() returns the LOCAL 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 LOCAL 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 string 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:

LOCAL TIME

Arguments:

Name Description

unit

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

temporalInstantValue

An expression of one of the following types: ZONED DATETIME, LOCAL DATETIME, ZONED TIME, LOCAL TIME.

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()).
Example 57. localtime.truncate()
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:00

12:00:00

12:31:00.002000000

12:31:14"

12:31:14.645000000

12:31:14.645876000

Rows: 1

time()

Details for using the time() function.

Getting the current ZONED TIME

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

Syntax:

time([{timezone}])

Returns:

ZONED 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).
Example 58. time()
Query
RETURN time() AS currentTime

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

Table 59. Result
currentTime

10:02:32.192Z

Rows: 1
Example 59. time()
Query
RETURN time({timezone: 'America/Los Angeles'}) AS currentTimeInLA

The current time of day in California is returned.

Table 60. Result
currentTimeInLA

03:02:32.233-07:00

Rows: 1

time.transaction()

time.transaction() returns the current ZONED 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:

ZONED TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 60. time.transaction()
Query
RETURN time.transaction() AS currentTime
Table 61. Result
currentTime

10:02:32.276Z

Rows: 1

time.statement()

time.statement() returns the current ZONED 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:

ZONED TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 61. time.statement()
Query
RETURN time.statement() AS currentTime
Table 62. Result
currentTime

10:02:32.317Z

Rows: 1
Example 62. time.statement()
Query
RETURN time.statement('America/Los Angeles') AS currentTimeInLA
Table 63. Result
currentTimeInLA

03:02:32.351-07:00

Rows: 1

time.realtime()

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

Syntax:

time.realtime([{timezone}])

Returns:

ZONED TIME

Arguments:

Name Description

timezone

A string expression that represents the time zone.
Example 63. time.realtime()
Query
RETURN time.realtime() AS currentTime
Table 64. Result
currentTime

10:02:32.436948Z

Rows: 1

Creating a ZONED TIME

time() returns a ZONED 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:

ZONED 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.
Example 64. time()
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.645876000+01:00

12:31:00+01:00

12:00:00+01:00

Rows: 5

Creating a ZONED TIME from a STRING

time() returns the ZONED TIME value obtained by parsing a STRING representation of a temporal value.

Syntax:

time(temporalValue)

Returns:

ZONED 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.
Example 65. time()
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.142000000+01:00

21:40:32.142000000Z

21:40:32+01:00

21:40:32-01:00

21:40:00-01:30

21:40:00Z

21:40:00-02:00

22:00:00+18:00

Rows: 8

Creating a ZONED TIME using other temporal values as components

time() returns the ZONED TIME value obtained by selecting and composing components from another temporal value. In essence, this allows a ZONED DATETIME, LOCAL DATETIME or LOCAL TIME value to be converted to a ZONED TIME, and for "missing" components to be provided.

Syntax:

time({time [, hour, ..., timezone]})

Returns:

ZONED TIME

Arguments:

Name Description

A single map consisting of the following:

time

A ZONED 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 ZONED TIME or ZONED DATETIME value as the time component also selects its time zone. If a LOCAL TIME or LOCAL DATETIME is selected instead, the default time zone is used. In any case, the time zone can be overridden explicitly.

Selecting a ZONED DATETIME or ZONED TIME as the time component and overwriting the time zone will adjust the local time to keep the same point in time.
Example 66. 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

Rows: 1

Truncating a ZONED TIME

time.truncate() returns the ZONED 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 ZONED 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 string 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:

ZONED TIME

Arguments:

Name Description

unit

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

temporalInstantValue

An expression of one of the following types: ZONED DATETIME, LOCAL DATETIME, ZONED TIME, LOCAL TIME.

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 ZONED TIME, ZONED DATETIME — a value with a time zone — and the time zone is overridden, no time conversion occurs.

If temporalInstantValue is one of LOCAL TIME, LOCAL DATETIME, 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()).
Example 67. 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:00-01:00

12:00:00-01:00

12:31:00-01:00

12:31:14-01:00

12:31:14.645000002-01:00

12:31:14.645876000-01:00

Rows: 1