Temporal values
Cypher® has built-in support for handling temporal values, which can be stored as properties on nodes and relationships in Neo4j databases. This section will discuss how Cypher handles time zones, before exploring temporal values in more detail.
|
Temporal value types
The following table lists the temporal value types and their supported components:
Type | Date support | Time support | Time zone support |
---|---|---|---|
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|
|
DATE
, LOCAL TIME
, ZONED TIME
, LOCAL DATETIME
, and ZONED DATETIME
are temporal instant types.
A temporal instant value expresses a point in time with varying degrees of precision.
By contrast, DURATION
is not a temporal instant type.
A DURATION
represents a temporal amount, capturing the difference in time between two instants, and can be negative.
DURATION
captures the amount of time between two instants, it does not capture a start time and end time.
Starting from Neo4j 5.9, some temporal types have been renamed. The table below shows the current as well as the old names of the temporal types.
Type | Old type name |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Time zones
Time zones are represented either as an offset from UTC, or as a logical identifier of a named time zone (these are based on the IANA time zone database). In either case, the time is stored as UTC internally, and the time zone offset is only applied when the time is presented. This means that temporal instants can be ordered without taking time zone into account. If, however, two times are identical in UTC, then they are ordered by timezone.
When creating a time using a named time zone, the offset from UTC is computed from the rules in the time zone database to create a time instant in UTC, and to ensure the named time zone is a valid one.
It is possible for time zone rules to change in the IANA time zone database. For example, there could be alterations to the rules for daylight savings time in a certain area. If this occurs after the creation of a temporal instant, the presented time could differ from the originally-entered time, insofar as the local timezone is concerned. However, the absolute time in UTC would remain the same.
There are three ways of specifying a time zone in Cypher:
-
Specifying the offset from UTC in hours and minutes (ISO 8601).
-
Specifying a named time zone.
-
Specifying both the offset and the time zone name (with the requirement that these match).
See specifying time zones for examples.
The named time zone form uses the rules of the IANA time zone database to manage daylight savings time (DST).
The default time zone of the database can be configured using the configuration option db.temporal.timezone
.
This configuration option influences the creation of temporal types for the following functions:
-
Getting the current date and time without specifying a time zone.
-
Creating a temporal type from its components without specifying a time zone.
-
Creating a temporal type by parsing a
STRING
without specifying a time zone. -
Creating a temporal type by combining or selecting values that do not have a time zone component, and without specifying a time zone.
-
Truncating a temporal value that does not have a time zone component, and without specifying a time zone.
Temporal instants
Specifying temporal instants
A temporal instant consists of three parts; the date
, the time
, and the timezone
.
These parts can be combined to produce the various temporal value types.
The character T
is a literal character.
Temporal instant type | Composition of parts |
---|---|
|
|
|
|
|
|
|
|
|
|
*When date
and time
are combined, date
must be complete; i.e. fully identify a particular day.
Specifying dates
Component | Format | Description |
---|---|---|
Year |
|
Specified with at least four digits (special rules apply in certain cases). |
Month |
|
Specified with a double digit number from |
Week |
|
Always prefixed with |
Quarter |
|
Always prefixed with |
Day of the month |
|
Specified with a double digit number from |
Day of the week |
|
Specified with a single digit number from |
Day of the quarter |
|
Specified with a double digit number from |
Ordinal day of the year |
|
Specified with a triple digit number from |
If the year is before 0000
or after 9999
, the following additional rules apply:
-
Minus sign,
-
must prefix any year before0000
, (e.g.-3000-01-01
). -
Plus sign,
+
must prefix any year after9999
, (e.g.+11000-01-01
). -
The year must be separated with
-
from the next component:-
if the next component is month, (e.g.
+11000-01
). -
if the next component is day of the year, (e.g.
+11000-123
).
-
If the year component is prefixed with either -
or +
, and is separated from the next component, Year
is allowed to contain up to nine digits.
Thus, the allowed range of years is between -999,999,999 and +999,999,999.
For all other cases, i.e. the year is between 0000
and 9999
(inclusive), Year
must have exactly four digits (the year component is interpreted as a year of the Common Era (CE)).
The following formats are supported for specifying dates:
Format | Description | Example | Interpretation of example |
---|---|---|---|
|
Calendar date: |
|
|
|
Calendar date: |
|
|
|
Calendar date: |
|
|
|
Calendar date: |
|
|
|
Week date: |
|
|
|
Week date: |
|
|
|
Week date: |
|
|
|
Week date: |
|
|
|
Quarter date: |
|
|
|
Quarter date: |
|
|
|
Quarter date: |
|
|
|
Quarter date: |
|
|
|
Ordinal date: |
|
|
|
Ordinal date: |
|
|
|
Year |
|
|
The smallest components can be omitted.
Cypher will assume omitted components to have their lowest possible value.
For example, 2013-06
will be interpreted as being the same date as 2013-06-01
.
Specifying times
Component | Format | Description |
---|---|---|
|
|
Specified with a double digit number from |
|
|
Specified with a double digit number from |
|
|
Specified with a double digit number from |
|
|
Specified with a number from |
Cypher does not support leap seconds; UTC-SLS (UTC with Smoothed Leap Seconds) is used to manage the difference in time between UTC and TAI (International Atomic Time).
The following formats are supported for specifying times:
Format | Description | Example | Interpretation of example |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The smallest components can be omitted.
For example, a time may be specified with Hour
and Minute
, leaving out Second
and fraction
.
On the other hand, specifying a time with Hour
and Second
, while leaving out Minute
, is not possible.
Specifying time zones
The time zone is specified in one of the following ways:
-
As an offset from UTC.
-
Using the
Z
shorthand for the UTC (±00:00
) time zone.
When specifying a time zone as an offset from UTC, the rules below apply:
-
The time zone always starts with either a plus (
+
) or minus (-
) sign.-
Positive offsets, i.e. time zones beginning with
+
, denote time zones east of UTC. -
Negative offsets, i.e. time zones beginning with
-
, denote time zones west of UTC.
-
-
A double-digit hour offset follows the
+
/-
sign. -
An optional double-digit minute offset follows the hour offset, optionally separated by a colon (
:
). -
The time zone of the International Date Line is denoted either by
+12:00
or-12:00
, depending on country.
When creating values of the ZONED DATETIME
temporal instant type, the time zone may also be specified using a named time zone, using the names from the IANA time zone database.
This may be provided either in addition to, or in place of the offset.
The named time zone is given last and is enclosed in square brackets ([]
).
Should both the offset and the named time zone be provided, the offset must match the named time zone.
The following formats are supported for specifying time zones:
Format | Description | Example | Supported for ZONED DATETIME |
Supported for ZONED TIME |
---|---|---|---|---|
|
UTC |
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
Components of temporal instants
Components of temporal instant values can be accessed as properties.
Component | Description | Type | Range/Format | DATE |
ZONED DATETIME |
LOCAL DATETIME |
ZONED TIME |
LOCAL TIME |
---|---|---|---|---|---|---|---|---|
|
The |
|
At least 4 digits.
For more information, see the rules for using the |
|||||
|
The quarter-of-the-year component. |
|
|
|||||
|
The month-of-the-year component. |
|
|
|||||
|
The week-of-the-year component.[2] |
|
|
|||||
|
The year that the week-of-year component belongs to.[3] |
|
At least 4 digits.
For more information, see the rules for using the |
|||||
|
The day-of-the-quarter component. |
|
|
|||||
|
The day-of-the-quarter component (alias for |
|
|
|||||
|
The day-of-the-month component. |
|
|
|||||
|
The day-of-the-year component. |
|
|
|||||
|
The day-of-the-week component (the first day of the week is Monday). |
|
|
|||||
|
The day-of-the-week component (alias for |
|
|
|||||
|
The hour component. |
|
|
|||||
|
The minute component. |
|
|
|||||
|
The second component.[4] |
|
|
|||||
|
The millisecond component. |
|
|
|||||
|
The microsecond component. |
|
|
|||||
|
The nanosecond component. |
|
|
|||||
|
The timezone component. |
|
Depending on how the time zone was specified, this is either a time zone name or an offset from UTC in the format |
|||||
|
The timezone offset. |
|
In the format |
|||||
|
The timezone offset in minutes. |
|
|
|||||
|
The timezone offset in seconds. |
|
|
|||||
|
The number of milliseconds between |
|
Positive for instants after and negative for instants before |
|||||
|
The number of seconds between |
|
Positive for instants after and negative for instants before |
Examples
Below are examples of parsing instant values using various temporal functions. More information about these temporal functions can be found here.
Parsing a ZONED DATETIME
using the calendar date format:
RETURN datetime('2015-06-24T12:50:35.556+0100') AS theDateTime
theDateTime |
---|
|
Rows: 1 |
Parsing a LOCAL DATETIME
using the ordinal date format:
RETURN localdatetime('2015185T19:32:24') AS theLocalDateTime
theLocalDateTime |
---|
|
Rows: 1 |
Parsing a DATE
using the week date format:
RETURN date('+2015-W13-4') AS theDate
theDate |
---|
|
Rows: 1 |
Parsing a ZONED TIME
:
RETURN time('125035.556+0100') AS theTime
theTime |
---|
|
Rows: 1 |
Parsing a LOCAL TIME
:
RETURN localtime('12:50:35.556') AS theLocalTime
theLocalTime |
---|
|
Rows: 1 |
The following query shows how to get the components of a DATE
value:
WITH date({year: 1984, month: 10, day: 11}) AS d
RETURN d.year, d.quarter, d.month, d.week, d.weekYear, d.day, d.ordinalDay, d.dayOfWeek, d.dayOfQuarter
d.year | d.quarter | d.month | d.week | d.weekYear | d.day | d.ordinalDay | d.dayOfWeek | d.dayOfQuarter |
---|---|---|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
Rows: 1 |
The following query shows how to get the date-related components of a ZONED DATETIME
value:
WITH datetime({
year: 1984, month: 11, day: 11,
hour: 12, minute: 31, second: 14, nanosecond: 645876123,
timezone: 'Europe/Stockholm'
}) AS d
RETURN d.year, d.quarter, d.month, d.week, d.weekYear, d.day, d.ordinalDay, d.dayOfWeek, d.dayOfQuarter
d.year | d.quarter | d.month | d.week | d.weekYear | d.day | d.ordinalDay | d.dayOfWeek | d.dayOfQuarter |
---|---|---|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
Rows: 1 |
The following query shows how to get the time-related components of a ZONED DATETIME
value:
WITH datetime({
year: 1984, month: 11, day: 11,
hour: 12, minute: 31, second: 14, nanosecond: 645876123,
timezone: 'Europe/Stockholm'
}) AS d
RETURN d.hour, d.minute, d.second, d.millisecond, d.microsecond, d.nanosecond
d.hour | d.minute | d.second | d.millisecond | d.microsecond | d.nanosecond |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
The following query shows how to get the epoch time and timezone-related components of a ZONED DATETIME
value:
WITH datetime({
year: 1984, month: 11, day: 11,
hour: 12, minute: 31, second: 14, nanosecond: 645876123,
timezone: 'Europe/Stockholm'
}) AS d
RETURN d.timezone, d.offset, d.offsetMinutes, d.epochSeconds, d.epochMillis
d.timezone | d.offset | d.offsetMinutes | d.epochSeconds | d.epochMillis |
---|---|---|---|---|
|
|
|
|
|
Rows: 1 |
Get the first day of the current year:
RETURN date.truncate('year') AS day
day |
---|
|
Rows: 1 |
Get the date of the Thursday in the week of a specific date:
RETURN date.truncate('week', date('2019-10-01'), {dayOfWeek: 4}) AS thursday
thursday |
---|
|
Rows: 1 |
Durations
Specifying durations
A DURATION
represents a temporal amount, capturing the difference in time between two instants, and can be negative.
The specification of a DURATION
is prefixed with a P
, and can use either a unit-based form or a date-and-time-based form:
-
Unit-based form:
P[nY][nM][nW][nD][T[nH][nM][nS]]
-
The square brackets (
[]
) denote an optional component (components with a zero value may be omitted). -
The
n
denotes a numeric value within the bounds of a 64-bit integer. -
The value of the last — and smallest — component may contain a decimal fraction.
-
Each component must be suffixed by a component identifier denoting the unit.
-
The unit-based form uses
M
as a suffix for both months and minutes. Therefore, time parts must always be preceded withT
, even when no components of the date part are given. -
The maximum total length of a duration is bounded by the number of seconds that can be held in a 64-bit integer.
-
-
Date-and-time-based form:
P<date>T<time>
.-
Unlike the unit-based form, this form requires each component to be within the bounds of a valid
LOCAL DATETIME
.
-
The following table lists the component identifiers for the unit-based form:
Component identifier | Description | Comments |
---|---|---|
|
Years |
|
|
Months |
Must be specified before |
|
Weeks |
|
|
Days |
|
|
Hours |
|
|
Minutes |
Must be specified after |
|
Seconds |
Components of durations
A DURATION
can have several components, each categorized into Months, Days, and Seconds groups.
Components of DURATION
values are truncated within their component groups as follows:
Component Group | Component | Description | Type | Details |
---|---|---|---|---|
Months |
|
The total number of years. |
|
Each set of |
|
The total number of quarters. |
|
Each year is counted as |
|
|
The total number of months. |
|
Each year is counted as |
|
Days |
|
The total number of weeks. |
|
Each set of |
|
The total number of days. |
|
Each week is counted as |
|
Seconds |
|
The total number of hours. |
|
Each set of |
|
The total number of minutes. |
|
Each hour is counted as |
|
|
The total number of seconds. |
|
Each hour is counted as |
|
|
The total number of milliseconds |
|
Each set of |
|
|
The total number of microseconds. |
|
Each millisecond is counted as |
|
|
The total number of nanoseconds. |
|
Each microsecond is counted as |
Please note that:
|
It is also possible to access the second order components of a component group bounded by the first order component of the group:
Component | Component Group | Description | Type |
---|---|---|---|
|
Months |
The number of quarters in the group that do not make a whole year. |
|
|
Months |
The number of months in the group that do not make a whole year. |
|
|
Months |
The number of months in the group that do not make a whole quarter. |
|
|
Days |
The number of days in the group that do not make a whole week. |
|
|
Seconds |
The number of minutes in the group that do not make a whole hour. |
|
|
Seconds |
The number of seconds in the group that do not make a whole minute. |
|
|
Seconds |
The number of milliseconds in the group that do not make a whole second. |
|
|
Seconds |
The number of microseconds in the group that do not make a whole second. |
|
|
Seconds |
The number of nanoseconds in the group that do not make a whole second |
|
Examples
Below are examples of parsing durations using the duration()
function.
More information can be found here.
14
days, 16
hours, and 12
minutesRETURN duration('P14DT16H12M') AS theDuration
theDuration |
---|
|
Rows: 1 |
5
months, 1
day, and 12
hoursRETURN duration('P5M1.5D') AS theDuration
theDuration |
---|
|
Rows: 1 |
45
secondsRETURN duration('PT0.75M') AS theDuration
theDuration |
---|
|
Rows: 1 |
2
weeks, 3
days, and 12
hoursRETURN duration('P2.5W') AS theDuration
theDuration |
---|
|
Rows: 1 |
DURATION
valueWITH duration({years: 1, months: 5, days: 111, minutes: 42}) AS d
RETURN d.years, d.quarters, d.quartersOfYear, d.months, d.monthsOfYear, d.monthsOfQuarter
d.years | d.quarters | d.quartersOfYear | d.months | d.monthsOfYear | d.monthsOfQuarter |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
d.quarters
has a value of 5
because the year of the duration has four quarters and there is another quarter in the five months.
d.months
has a value of 17
because it adds the 12 months in the year of the duration to the five months.
d.quartersOfYear
is the remaining quarter, counting towards the next full year. Similarly, d.monthsOfYear
and d.monthsOfQuarter
count towards the next full year and quarter respectively.
See tables First order DURATION
components and Second order DURATION
components in Components of durations.
DURATION
valueWITH duration({months: 5, days: 25, hours: 1}) AS d
RETURN d.weeks, d.days, d.daysOfWeek
d.weeks | d.days | d.daysOfWeek |
---|---|---|
|
|
|
Rows: 1 |
d.weeks
has a value of 3 because the 25 days from the query are three full weeks (or 21 days). d.daysOfWeek
are the remaining days, counting towards the next full week.
See tables First order DURATION
components and Second order DURATION
components in Components of durations.
DURATION
valueWITH duration({
years: 1, months:1, days:1, hours: 1,
minutes: 1, seconds: 1, nanoseconds: 111111111
}) AS d
RETURN d.hours, d.minutes, d.seconds, d.milliseconds, d.microseconds, d.nanoseconds
d.hours | d.minutes | d.seconds | d.milliseconds | d.microseconds | d.nanoseconds |
---|---|---|---|---|---|
|
|
|
|
|
|
Rows: 1 |
d.minutes
is the sum of 60 minutes of the hour and the one minute from the query as both duration.hours
and duration.minutes
are both seconds-based components.
Similarly, d.seconds
, d.milliseconds
, d.microseconds
and d.nanoseconds
are sum values of the relevant seconds-based components from the query.
d.hours
does not take the day from the query into account because duration.days
is a days-based component.
See table First order DURATION
components in Components of durations.
DURATION
valueWITH duration({
years: 1, months:1, days:1,
hours: 1, minutes: 1, seconds: 1, nanoseconds: 111111111
}) AS d
RETURN d.minutesOfHour, d.secondsOfMinute, d.millisecondsOfSecond, d.microsecondsOfSecond, d.nanosecondsOfSecond
d.minutesOfHour | d.secondsOfMinute | d.millisecondsOfSecond | d.microsecondsOfSecond | d.nanosecondsOfSecond |
---|---|---|---|---|
|
|
|
|
|
Rows: 1 |
The returned values all count towards the next full hour, minute or second respectively.
For example, d.microsecondsOfSecond
has a value of 111111
because it is the 111111111
nanoseconds from the query in microseconds (rounded down) but it is not another full second.
See table Second order DURATION
components in Components of durations.
RETURN duration({days: 1, hours: 12}) AS theDuration
theDuration |
---|
|
Rows: 1 |
DURATION
between two temporal instantsRETURN duration.between(date('1984-10-11'), date('2015-06-24')) AS theDuration
theDuration |
---|
|
Rows: 1 |
DATE
valuesRETURN duration.inDays(date('2014-10-11'), date('2015-08-06')) AS theDuration
theDuration |
---|
|
Rows: 1 |
DATE
of the last day of the next monthRETURN date.truncate('month', date() + duration('P2M')) - duration('P1D') AS lastDay
lastDay |
---|
|
Rows: 1 |
DURATION
to a DATE
RETURN time('13:42:19') + duration({days: 1, hours: 12}) AS theTime
theTime |
---|
|
Rows: 1 |
DURATION
valuesRETURN duration({days: 2, hours: 7}) + duration({months: 1, hours: 18}) AS theDuration
theDuration |
---|
|
Rows: 1 |
DURATION
by a numberRETURN duration({hours: 5, minutes: 21}) * 14 AS theDuration
theDuration |
---|
|
Rows: 1 |
DURATION
by a numberRETURN duration({hours: 3, minutes: 16}) / 2 AS theDuration
theDuration |
---|
|
Rows: 1 |
WITH
datetime('2015-07-21T21:40:32.142+0100') AS date1,
datetime('2015-07-21T17:12:56.333+0100') AS date2
RETURN
CASE
WHEN date1 < date2 THEN date1 + duration("P1D") > date2
ELSE date2 + duration("P1D") > date1
END AS lessThanOneDayApart
lessThanOneDayApart |
---|
|
Rows: 1 |
RETURN ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"][date().month-1] AS month
month |
---|
|
Rows: 1 |
Temporal indexing
All temporal types can be indexed, and thereby support exact lookups for equality predicates. Indexes for temporal instant types additionally support range lookups.