4.9.4. LocalDateTime: localdatetime()

Details for using the localdatetime() function.

4.9.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 4.122. Result
now

1 row

2019-07-04T10:36:55.811

Try this query live.  none RETURN localdatetime() AS now

Query. 

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

The current local date and time in California is returned.

Table 4.123. Result
now

1 row

2019-07-04T03:36:55.821

Try this query live.  none RETURN localdatetime({timezone: 'America/Los Angeles'}) AS now

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 4.124. Result
now

1 row

2019-07-04T10:36:55.822

Try this query live.  none RETURN localdatetime.transaction() AS now

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 4.125. Result
now

1 row

2019-07-04T10:36:55.839

Try this query live.  none RETURN localdatetime.statement() AS now

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 4.126. Result
now

1 row

2019-07-04T10:36:55.847

Try this query live.  none RETURN localdatetime.realtime() AS now

Query. 

RETURN localdatetime.realtime('America/Los Angeles') AS nowInLA

Table 4.127. Result
nowInLA

1 row

2019-07-04T03:36:55.857

Try this query live.  none RETURN localdatetime.realtime('America/Los Angeles') AS nowInLA

4.9.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 4.128. Result
theDate

1 row

1984-10-11T12:31:14.123456789

Try this query live.  none RETURN localdatetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789}) AS theDate

4.9.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 4.129. Result
theDate

1 row

1984-03-07T12:31:14.645

Try this query live.  none RETURN localdatetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, millisecond: 645}) AS theDate

4.9.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 4.130. Result
theDate

1 row

1984-08-14T12:31:14.645876123

Try this query live.  none RETURN localdatetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, nanosecond: 645876123}) AS theDate

4.9.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 4.131. Result
theDate

1 row

1984-07-20T12:31:14.645876

Try this query live.  none RETURN localdatetime({year:1984, ordinalDay:202, hour:12, minute:31, second:14, microsecond: 645876}) AS theDate

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

localdatetime(null) returns the current date and time.

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

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 4.132. Result
theDate

4 rows

2015-07-21T21:40:32.142

2015-07-21T21:40:32.142

2015-07-21T21:40:32

2015-07-21T21:00

Try this query live.  none UNWIND [ localdatetime('2015-07-21T21:40:32.142'), localdatetime('2015-W30-2T214032.142'), localdatetime('2015-202T21:40:32'), localdatetime('2015202T21') ] AS theDate RETURN theDate

4.9.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 4.133. Result
dateHHMMSS dateDDHHMMSS

1 row

1984-10-11T10:10:10

1984-10-28T10:10:10

Try this query live.  none 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

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 4.134. Result
YYYYMMDDTime YYYYMMDDTimeSS

1 row

1984-10-11T12:31:14.645876

1984-10-11T12:31:42.645876

Try this query live.  none 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

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 4.135. Result
dateTime dateTimeDDSS

1 row

1984-10-11T12:31:14.645876

1984-10-28T12:31:42.645876

Try this query live.  none 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

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 4.136. Result
dateTime dateTimeDDSS

1 row

1984-10-11T12:00

1984-10-28T12:00:42

Try this query live.  none 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

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

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 4.137. Result
truncMillenium truncYear truncMonth truncDay truncHour truncSecond

1 row

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

Try this query live.  none 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