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
.
The following functions are included on this page:
DATE |
ZONED DATETIME |
LOCAL DATETIME |
LOCAL TIME |
ZONED TIME |
---|---|---|---|---|
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 aZONED TIME
to create aZONED DATETIME
), or -
selecting parts from a temporal value (such as selecting the
DATE
from aZONED DATETIME
); the extractors — groups of components which can be selected — are:-
date
— contains all components for aDATE
(conceptually year, month and day). -
time
— contains all components for aZONED 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 containtimezone
(and atimezone
is not explicitly specified) thetimezone
is also selected. -
datetime
— selects all components, and is useful for overriding specific components. Analogously totime
, if the type being created and the type from which the time component is being selected both containtimezone
(and atimezone
is not explicitly specified) thetimezone
is also selected.
-
-
In effect, this allows for the conversion between different temporal types, and allowing for 'missing' components to be specified.
-
Function | DATE |
ZONED TIME |
LOCAL TIME |
ZONED DATETIME |
LOCAL DATETIME |
---|---|---|---|---|---|
Getting the current value. |
|||||
Creating a calendar-based (Year-Month-Day) value. |
|||||
Creating a week-based (Year-Week-Day) value. |
|||||
Creating a quarter-based (Year-Quarter-Day) value. |
|||||
Creating an ordinal (Year-Day) value. |
|||||
Creating a value from time components. |
|||||
Creating a value from other temporal values using extractors (i.e. converting between different types). |
|||||
Creating a value from a |
|||||
Creating a value from a timestamp. |
All the temporal instant types — including those that do not contain timezone information support such as |
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 |
||||
Time |
||||
LocalTime |
||||
DateTime |
||||
LocalDateTime |
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 smaller 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 smaller than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these smaller components.
The following table lists the supported truncation units and the corresponding sub-functions:
Truncation unit | DATE |
ZONED TIME |
LOCAL TIME |
ZONED DATETIME |
LOCAL DATETIME |
---|---|---|---|---|---|
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
|||||
|
date()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
Either a string representation of a temporal value, a map containing the single key 'timezone', or a map containing temporal values ('date', 'year', 'month', 'day', 'week', 'dayOfWeek', 'quarter', 'dayOfQuarter', 'ordinalDay') as components. |
|
Returns |
|
Name | Description |
---|---|
|
A |
|
An expression consisting of at least four digits that specifies the year. |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
If no parameters are provided, |
If no timezone is specified, the local timezone will be used. |
The day of the month component will default to |
The month component will default to |
If |
The day of the week component will default to |
The week component will default to |
If |
The day of the quarter component will default to |
The quarter component will default to |
If |
The ordinal day of the year component will default to |
String representations of temporal values must comply with the format defined for dates. |
String representations of temporal values must denote a valid date; i.e. a temporal value denoting |
|
If any of the optional parameters are provided, these will override the corresponding components of |
|
RETURN date() AS currentDate
The current date is returned.
currentDate |
---|
|
Rows: 1 |
RETURN date({timezone: 'America/Los Angeles'}) AS currentDateInLA
The current date in California is returned.
currentDateInLA |
---|
|
Rows: 1 |
Creating DATE
values
DATE
UNWIND [
date({year: 1984, month: 10, day: 11}),
date({year: 1984, month: 10}),
date({year: 1984})
] AS theDate
RETURN theDate
theDate |
---|
|
|
|
Rows: 3 |
DATE
UNWIND [
date({year: 1984, week: 10, dayOfWeek: 3}),
date({year: 1984, week: 10}),
date({year: 1984})
] AS theDate
RETURN theDate
theDate |
---|
|
|
|
Rows: 3 |
DATE
UNWIND [
date({year: 1984, quarter: 3, dayOfQuarter: 45}),
date({year: 1984, quarter: 3}),
date({year: 1984})
] AS theDate
RETURN theDate
theDate |
---|
|
|
|
Rows: 3 |
DATE
UNWIND [
date({year: 1984, ordinalDay: 202}),
date({year: 1984})
] AS theDate
RETURN theDate
The date corresponding to 11 February 1984
is returned.
theDate |
---|
|
|
Rows: 2 |
DATE
using other temporal values as componentsUNWIND [
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
dateOnly | +dateDay |
---|---|
|
|
|
|
|
|
Rows: 3 |
DATE
from a STRING
UNWIND [
date('2015-07-21'),
date('2015-07'),
date('201507'),
date('2015-W30-2'),
date('2015202'),
date('2015')
] AS theDate
RETURN theDate
theDate |
---|
|
|
|
|
|
|
Rows: 6 |
date.realtime()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
This returned DATE
will be the live clock of the system.
RETURN date.realtime() AS currentDate
currentDate |
---|
|
Rows: 1 |
RETURN date.realtime('America/Los Angeles') AS currentDateInLA
currentDateInLA |
---|
|
Rows: 1 |
date.statement()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
This returned DATE
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.
RETURN date.statement() AS currentDate
currentDate |
---|
|
Rows: 1 |
date.transaction()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned DATE
will be the same for each invocation within the same transaction.
However, a different value may be produced for different transactions.
RETURN date.transaction() AS currentDate
currentDate |
---|
|
Rows: 1 |
date.truncate()
Syntax |
|
||
Description |
Truncates the given temporal value to a |
||
Arguments |
Name |
Type |
Description |
|
|
A string representing one of the following: 'day', 'week', 'month', 'weekYear', 'quarter', 'year', 'decade', 'century', 'millennium'. |
|
|
|
The date to be truncated using either |
|
|
|
A list of time components smaller than those specified in |
|
Returns |
|
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 smaller 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 smaller than the truncation unit.
This will have the effect of overriding the default values which would otherwise have been set for these smaller 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
).
Any component that is provided in |
Any component that is not contained in |
If |
If |
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
truncMillenium | truncCentury | truncDecade | truncYear | truncWeekYear | truncQuarter | truncMonth | truncWeek | truncDay |
---|---|---|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
Rows: 1 |
datetime()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
Either a string representation of a temporal value, a map containing the single key 'timezone', or a map containing temporal values ('year', 'month', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond', 'nanosecond', 'timezone') as components. |
|
Returns |
|
Name | Description |
---|---|
|
An expression consisting of at least four digits that specifies the year. |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An expression that specifies the timezone. |
|
A numeric value representing the number of seconds from the UNIX epoch in the UTC timezone. |
|
A numeric value representing the number of milliseconds from the UNIX epoch in the UTC timezone. |
If no parameters are provided, |
The month component will default to |
The day of the month component will default to |
The hour component will default to |
The minute component will default to |
The second component will default to |
Any missing |
The timezone component will default to the configured default timezone if |
If |
The smallest components in the set |
One or more of |
String representations of temporal values must comply with the format defined for dates, times and time zones. |
String representations of temporal values must denote a valid date; i.e. a temporal value denoting |
If any of the optional parameters are provided, these will override the corresponding components of |
|
Selecting a |
Selecting a |
Selecting a |
|
|
RETURN datetime() AS currentDateTime
The current date and time using the local timezone is returned.
currentDateTime |
---|
|
Rows: 1 |
RETURN datetime({timezone: 'America/Los Angeles'}) AS currentDateTimeInLA
The current date and time of day in California is returned.
currentDateTimeInLA |
---|
|
Rows: 1 |
Creating ZONED DATETIME
values
ZONED DATETIME
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
theDate |
---|
|
|
|
|
|
|
|
|
Rows: 8 |
ZONED DATETIME
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
theDate |
---|
|
|
|
|
|
|
|
Rows: 7 |
ZONED DATETIME
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
theDate |
---|
|
|
|
|
Rows: 4 |
ZONED DATETIME
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
theDate |
---|
|
|
|
|
Rows: 4 |
ZONED DATETIME
from a STRING
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
theDate |
---|
|
|
|
|
|
|
|
|
Rows: 8 |
ZONED DATETIME
using other temporal values as componentsThe following query shows the various usages of datetime({date [, year, ..., timezone]})
.
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
dateHHMMSS | dateHHMMSSTimezone | dateDDHHMMSS | dateDDHHMMSSTimezone |
---|---|---|---|
|
|
|
|
Rows: 1 |
ZONED DATETIME
using other temporal values as componentsThe following query shows the various usages of datetime({time [, year, …, timezone]})
.
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
YYYYMMDDTime | +YYYYMMDDTimeTimezone | YYYYMMDDTimeSS | YYYYMMDDTimeSSTimezone |
---|---|---|---|
|
|
|
|
Rows: 1 |
ZONED DATETIME
using other temporal values as componentsThe 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.
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
dateTime | dateTimeTimezone | dateTimeDDSS | dateTimeDDSSTimezone |
---|---|---|---|
|
|
|
|
Rows: 1 |
ZONED DATETIME
using other temporal values as componentsThe following query shows the various usages of datetime({datetime [, year, ..., timezone]})
.
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
dateTime | dateTimeTimezone | dateTimeDDSS | dateTimeDDSSTimezone |
---|---|---|---|
|
|
|
|
Rows: 1 |
ZONED DATETIME
from UNIX epoch (epocSeconds
)datetime()
returns the ZONED DATETIME
value at the specified number of seconds or milliseconds from the UNIX epoch in the UTC timezone.
Conversions to other temporal instant types from UNIX epoch representations can be achieved by transforming a ZONED DATETIME
value to one of these types.
RETURN datetime({epochSeconds: timestamp() / 1000, nanosecond: 23}) AS theDate
theDate |
---|
|
Rows: 1 |
ZONED DATETIME
from UNIX epoch (epocMillis
)RETURN datetime({epochMillis: 424797300000}) AS theDate
theDate |
---|
|
Rows: 1 |
datetime.fromEpoch()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
The number of seconds from the UNIX epoch in the UTC timezone. |
|
|
|
The number of nanoseconds from the UNIX epoch in the UTC timezone. This can be added to seconds. |
|
Returns |
|
WITH datetime.fromepoch(1683000000, 123456789) AS dateTimeFromEpoch
RETURN dateTimeFromEpoch
dateTimeFromEpoch |
---|
|
Rows: 1 |
datetime.fromEpochMillis()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
The number of milliseconds from the UNIX epoch in the UTC timezone. |
|
Returns |
|
WITH datetime.fromepochmillis(1724198400000) AS dateTimeFromMillis
RETURN dateTimeFromMillis
dateTimeFromMillis |
---|
|
Rows: 1 |
datetime.realtime()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned ZONED DATETIME
will be the live clock of the system.
RETURN datetime.realtime() AS currentDateTime
currentDateTime |
---|
|
Rows: 1 |
datetime.statement()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
This returned ZONED DATETIME
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.
RETURN datetime.statement() AS currentDateTime
currentDateTime |
---|
|
Rows: 1 |
datetime.transaction()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned ZONED DATETIME
value will be the same for each invocation within the same transaction.
However, a different value may be produced for different transactions.
RETURN datetime.transaction() AS currentDateTime
currentDateTime |
---|
|
Rows: 1 |
RETURN datetime.transaction('America/Los Angeles') AS currentDateTimeInLA
currentDateTimeInLA |
---|
|
Rows: 1 |
datetime.truncate()
Syntax |
|
||
Description |
Truncates the given temporal value to a |
||
Arguments |
Name |
Type |
Description |
|
|
A string representing one of the following: 'microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'weekYear', 'quarter', 'year', 'decade', 'century', 'millennium'. |
|
|
|
The date to be truncated using either |
|
|
|
A list of time components smaller than those specified in |
|
Returns |
|
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 smaller 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 smaller than the truncation unit.
This will have the effect of overriding the default values which would otherwise have been set for these smaller 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
).
|
The timezone of |
If |
If |
Any component that is provided in |
Any component that is not contained in |
If |
If |
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
truncMillenium | truncYear | truncMonth | truncDay | truncHour | truncSecond |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
localdatetime()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
Either a string representation of a temporal value, a map containing the single key 'timezone', or a map containing temporal values ('year', 'month', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond', 'nanosecond') as components. |
|
Returns |
|
Name | Description |
---|---|
|
|
|
An expression consisting of at least four digits that specifies the year. |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
If no parameters are provided, |
The month component will default to |
The day of the month component will default to |
The hour component will default to |
The minute component will default to |
The second component will default to |
Any missing |
If |
The smallest components in the set |
One or more of |
String representations of temporal values must comply with the format defined for dates and times. |
String representations of temporal values must denote a valid date; i.e. a temporal value denoting |
|
If any of the optional parameters are provided, these will override the corresponding components of |
|
RETURN localdatetime() AS now
The current local date and time (i.e. in the local timezone) is returned.
now |
---|
|
Rows: 1 |
RETURN localdatetime({timezone: 'America/Los Angeles'}) AS now
The current local date and time in California is returned.
now |
---|
|
Rows: 1 |
Creating LOCAL DATETIME
values
LOCAL DATETIME
RETURN
localdatetime({
year: 1984, month: 10, day: 11,
hour: 12, minute: 31, second: 14, millisecond: 123, microsecond: 456, nanosecond: 789
}) AS theDate
theDate |
---|
|
Rows: 1 |
LOCAL DATETIME
RETURN
localdatetime({
year: 1984, week: 10, dayOfWeek: 3,
hour: 12, minute: 31, second: 14, millisecond: 645
}) AS theDate
theDate |
---|
|
Rows: 1 |
ZONED DATETIME
RETURN
localdatetime({
year: 1984, quarter: 3, dayOfQuarter: 45,
hour: 12, minute: 31, second: 14, nanosecond: 645876123
}) AS theDate
theDate |
---|
|
Rows: 1 |
LOCAL DATETIME
RETURN
localdatetime({
year: 1984, ordinalDay: 202,
hour: 12, minute: 31, second: 14, microsecond: 645876
}) AS theDate
theDate |
---|
|
Rows: 1 |
LOCAL DATETIME
from a STRING
UNWIND [
localdatetime('2015-07-21T21:40:32.142'),
localdatetime('2015-W30-2T214032.142'),
localdatetime('2015-202T21:40:32'),
localdatetime('2015202T21')
] AS theDate
RETURN theDate
theDate |
---|
|
|
|
|
Rows: 4 |
LOCAL DATETIME
using other temporal values as componentsThe following query shows the various usages of localdatetime({date [, year, ..., nanosecond]})
.
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
dateHHMMSS | dateDDHHMMSS |
---|---|
|
|
Rows: 1 |
LOCAL DATETIME
using other temporal values as componentsThe following query shows the various usages of localdatetime({time [, year, ..., nanosecond]})
.
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
YYYYMMDDTime | YYYYMMDDTimeSS |
---|---|
|
|
Rows: 1 |
LOCAL DATETIME
using other temporal values as componentsThe 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.
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
dateTime | dateTimeDDSS |
---|---|
|
|
Rows: 1 |
LOCAL DATETIME
using other temporal values as componentsThe following query shows the various usages of localdatetime({datetime [, year, ..., nanosecond]})
.
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
dateTime | dateTimeDDSS |
---|---|
|
|
Rows: 1 |
localdatetime.realtime()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned LOCAL DATETIME
will be the live clock of the system.
RETURN localdatetime.realtime() AS now
now |
---|
|
Rows: 1 |
RETURN localdatetime.realtime('America/Los Angeles') AS nowInLA
nowInLA |
---|
|
Rows: 1 |
localdatetime.statement()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned LOCAL DATETIME
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.
RETURN localdatetime.statement() AS now
now |
---|
|
Rows: 1 |
localdatetime.transaction()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned LOCAL DATETIME
will be the same for each invocation within the same transaction.
However, a different value may be produced for different transactions.
RETURN localdatetime.transaction() AS now
now |
---|
|
Rows: 1 |
localdatetime.truncate()
Syntax |
|
||
Description |
Truncates the given temporal value to a |
||
Arguments |
Name |
Type |
Description |
|
|
A string representing one of the following: 'microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'weekYear', 'quarter', 'year', 'decade', 'century', 'millennium'. |
|
|
|
The date to be truncated using either |
|
|
|
A list of time components smaller than those specified in |
|
Returns |
|
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 smaller 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 smaller than the truncation unit.
This will have the effect of overriding the default values which would otherwise have been set for these smaller 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
).
|
Any component that is provided in |
Any component that is not contained in |
If |
If |
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
truncMillenium | truncYear | truncMonth | truncDay | truncHour | truncSecond |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
localtime()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
Either a string representation of a temporal value, a map containing the single key 'timezone', or a map containing temporal values ('hour, 'minute', 'second', 'millisecond', 'microsecond', 'nanosecond' as components. |
|
Returns |
|
Name | Description |
---|---|
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
If no parameters are provided, |
The hour component will default to |
The minute component will default to |
The second component will default to |
Any missing |
If |
The smallest components in the set |
One or more of |
String representations of temporal values must comply with the format defined for times. |
String representations of temporal values must denote a valid time; i.e. a temporal value denoting |
|
If any of the optional parameters are provided, these will override the corresponding components of |
|
RETURN localtime() AS now
The current local time (i.e. in the local timezone) is returned.
now |
---|
|
Rows: 1 |
RETURN localtime({timezone: 'America/Los Angeles'}) AS nowInLA
The current local time in California is returned.
nowInLA |
---|
|
Rows: 1 |
Creating LOCAL TIME
values
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
theTime |
---|
|
|
|
Rows: 3 |
LOCAL TIME
from a STRING
UNWIND [
localtime('21:40:32.142'),
localtime('214032.142'),
localtime('21:40'),
localtime('21')
] AS theTime
RETURN theTime
theTime |
---|
|
|
|
|
Rows: 4 |
LOCAL TIME
using other temporal values as componentsWITH 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
timeOnly | timeSS |
---|---|
|
|
Rows: 1 |
localtime.realtime()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned LOCAL TIME
will be the live clock of the system.
RETURN localtime.realtime() AS now
now |
---|
|
Rows: 1 |
localtime.statement()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
This returned LOCAL TIME
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.
RETURN localtime.statement() AS now
now |
---|
|
Rows: 1 |
RETURN localtime.statement('America/Los Angeles') AS nowInLA
nowInLA |
---|
|
Rows: 1 |
localtime.transaction()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned LOCAL TIME
will be the same for each invocation within the same transaction.
However, a different value may be produced for different transactions.
RETURN localtime.transaction() AS now
now |
---|
|
Rows: 1 |
localtime.truncate()
Syntax |
|
||
Description |
Truncates the given temporal value to a |
||
Arguments |
Name |
Type |
Description |
|
|
A string representing one of the following: 'day', 'week', 'month', 'weekYear', 'quarter', 'year', 'decade', 'century', 'millennium'. |
|
|
|
The date to be truncated using either |
|
|
|
A list of time components smaller than those specified in |
|
Returns |
|
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 smaller 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 smaller than the truncation unit.
This will have the effect of overriding the default values which would otherwise have been set for these smaller 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
).
Truncating time to day — i.e. |
Any component that is provided in |
Any component that is not contained in |
If |
If |
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
truncDay | truncHour | truncMinute | truncSecond | truncMillisecond | truncMicrosecond |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
time()
Syntax |
|
||
Description |
Creates a |
||
Arguments |
Name |
Type |
Description |
|
|
Either a string representation of a temporal value, a map containing the single key 'timezone', or a map containing temporal values ('hour', 'minute', 'second', 'millisecond', 'microsecond', 'nanosecond', 'timezone') as components. |
|
Returns |
|
Name | Description |
---|---|
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An integer between |
|
An expression that specifies the timezone. |
If no parameters are provided, |
The hour component will default to |
The minute component will default to |
The second component will default to |
Any missing |
The timezone component will default to the configured default timezone if |
If |
The smallest components in the set |
One or more of |
String representations of temporal values must comply with the format defined for times and time zones. |
The timezone component will default to the configured default timezone if it is omitted. |
String representations of temporal values must denote a valid time; i.e. a |
|
If any of the optional parameters are provided, these will override the corresponding components of |
|
Selecting a |
Selecting a |
RETURN time() AS currentTime
The current time of day using the local timezone is returned.
currentTime |
---|
|
Rows: 1 |
RETURN time({timezone: 'America/Los Angeles'}) AS currentTimeInLA
The current time of day in California is returned.
currentTimeInLA |
---|
|
Rows: 1 |
Creating ZONED TIME
values
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
theTime |
---|
|
|
|
|
|
Rows: 5 |
ZONED TIME
from a STRING
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
theTime |
---|
|
|
|
|
|
|
|
|
Rows: 8 |
ZONED TIME
using other temporal values as componentsWITH 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
timeOnly | timeTimezone | timeSS | timeSSTimezone |
---|---|---|---|
|
|
|
|
Rows: 1 |
time.realtime()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned ZONED TIME
will be the live clock of the system.
RETURN time.realtime() AS currentTime
currentTime |
---|
|
Rows: 1 |
time.statement()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned ZONED TIME
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.
RETURN time.statement() AS currentTime
currentTime |
---|
|
Rows: 1 |
RETURN time.statement('America/Los Angeles') AS currentTimeInLA
currentTimeInLA |
---|
|
Rows: 1 |
time.transaction()
Syntax |
|
||
Description |
Returns the current |
||
Arguments |
Name |
Type |
Description |
|
|
A string value representing a timezone. |
|
Returns |
|
The returned ZONED TIME
will be the same for each invocation within the same transaction.
However, a different value may be produced for different transactions.
RETURN time.transaction() AS currentTime
currentTime |
---|
|
Rows: 1 |
time.truncate()
Syntax |
|
||
Description |
Truncates the given temporal value to a |
||
Arguments |
Name |
Type |
Description |
|
|
A string representing one of the following: 'microsecond', 'millisecond', 'second', 'minute', 'hour', 'day'. |
|
|
|
The date to be truncated using either |
|
|
|
A list of time components smaller than those specified in |
|
Returns |
|
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 smaller 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 smaller than the truncation unit.
This will have the effect of overriding the default values which would otherwise have been set for these smaller 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
).
Truncating time to day — i.e. |
The timezone of |
If |
If |
Any component that is provided in |
Any component that is not contained in |
If |
If |
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
truncDay | truncHour | truncMinute | truncSecond | truncMillisecond | truncMicrosecond |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |