Temporal (Date/Time) values

Refer to Temporal functions - instant types for information regarding temporal functions allowing for the creation and manipulation of temporal values.

Refer to Temporal operators for information regarding temporal operators.

Refer to Ordering and comparison of values for information regarding the comparison and ordering of temporal values.

Introduction

The following table depicts the temporal value types and supported components:

Type Date support Time support Time zone support

Date

X

Time

X

X

LocalTime

X

DateTime

X

X

X

LocalDateTime

X

X

Duration

-

-

-

Date, Time, LocalTime, DateTime and LocalDateTime 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 only captures the amount of time between two instants, and thus does not encapsulate a start time and end time.

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)

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 may then be combined to produce the various temporal value types. Literal characters are denoted in bold.

Temporal instant type Composition of parts

Date

<date>

Time

<time><timezone> or T<time><timezone>

LocalTime

<time> or T<time>

DateTime*

<date>T<time><timezone>

LocalDateTime*

<date>T<time>

*When date and time are combined, date must be complete; i.e. fully identify a particular day.

Specifying dates

Component Format Description

Year

YYYY

Specified with at least four digits (special rules apply in certain cases)

Month

MM

Specified with a double digit number from 01 to 12

Week

ww

Always prefixed with W and specified with a double digit number from 01 to 53

Quarter

q

Always prefixed with Q and specified with a single digit number from 1 to 4

Day of the month

DD

Specified with a double digit number from 01 to 31

Day of the week

D

Specified with a single digit number from 1 to 7

Day of the quarter

DD

Specified with a double digit number from 01 to 92

Ordinal day of the year

DDD

Specified with a triple digit number from 001 to 366

If the year is before 0000 or after 9999, the following additional rules apply:

  • - must prefix any year before 0000

  • + must prefix any year after 9999

  • The year must be separated from the next component with the following characters:

    • - if the next component is month or day of the year

    • Either - or W if the next component is week of the year

    • Q if the next component is quarter of the year

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

YYYY-MM-DD

Calendar date: Year-Month-Day

2015-07-21

2015-07-21

YYYYMMDD

Calendar date: Year-Month-Day

20150721

2015-07-21

YYYY-MM

Calendar date: Year-Month

2015-07

2015-07-01

YYYYMM

Calendar date: Year-Month

201507

2015-07-01

YYYY-Www-D

Week date: Year-Week-Day

2015-W30-2

2015-07-21

YYYYWwwD

Week date: Year-Week-Day

2015W302

2015-07-21

YYYY-Www

Week date: Year-Week

2015-W30

2015-07-20

YYYYWww

Week date: Year-Week

2015W30

2015-07-20

YYYY-Qq-DD

Quarter date: Year-Quarter-Day

2015-Q2-60

2015-05-30

YYYYQqDD

Quarter date: Year-Quarter-Day

2015Q260

2015-05-30

YYYY-Qq

Quarter date: Year-Quarter

2015-Q2

2015-04-01

YYYYQq

Quarter date: Year-Quarter

2015Q2

2015-04-01

YYYY-DDD

Ordinal date: Year-Day

2015-202

2015-07-21

YYYYDDD

Ordinal date: Year-Day

2015202

2015-07-21

YYYY

Year

2015

2015-01-01

The least significant 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

Hour

HH

Specified with a double digit number from 00 to 23

Minute

MM

Specified with a double digit number from 00 to 59

Second

SS

Specified with a double digit number from 00 to 59

fraction

sssssssss

Specified with a number from 0 to 999999999. It is not required to specify trailing zeros. fraction is an optional, sub-second component of Second. This can be separated from Second using either a full stop (.) or a comma (,). The fraction is in addition to the two digits of Second.

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

HH:MM:SS.sssssssss

Hour:Minute:Second.fraction

21:40:32.142

21:40:32.142

HHMMSS.sssssssss

Hour:Minute:Second.fraction

214032.142

21:40:32.142

HH:MM:SS

Hour:Minute:Second

21:40:32

21:40:32.000

HHMMSS

Hour:Minute:Second

214032

21:40:32.000

HH:MM

Hour:Minute

21:40

21:40:00.000

HHMM

Hour:Minute

2140

21:40:00.000

HH

Hour

21

21:00:00.000

The least significant 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 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 DateTime Supported for Time

Z

UTC

Z

X

X

±HH:MM

Hour:Minute

+09:30

X

X

±HH:MM[ZoneName]

Hour:Minute[ZoneName]

+08:45[Australia/Eucla]

X

±HHMM

Hour:Minute

+0100

X

X

±HHMM[ZoneName]

Hour:Minute[ZoneName]

+0200[Africa/Johannesburg]

X

±HH

Hour

-08

X

X

±HH[ZoneName]

Hour[ZoneName]

+08[Asia/Singapore]

X

[ZoneName]

[ZoneName]

[America/Regina]

X

Examples

We show below examples of parsing temporal instant values using various formats. For more details, refer to An overview of temporal instant type creation.

Parsing a DateTime using the calendar date format:

Query
RETURN datetime('2015-06-24T12:50:35.556+0100') AS theDateTime
Table 1. Result
theDateTime

2015-06-24T12:50:35.556+01:00

Rows: 1

Parsing a LocalDateTime using the ordinal date format:

Query
RETURN localdatetime('2015185T19:32:24') AS theLocalDateTime
Table 2. Result
theLocalDateTime

2015-07-04T19:32:24

Rows: 1

Parsing a Date using the week date format:

Query
RETURN date('+2015-W13-4') AS theDate
Table 3. Result
theDate

2015-03-26

Rows: 1

Parsing a Time:

Query
RETURN time('125035.556+0100') AS theTime
Table 4. Result
theTime

12:50:35.556+01:00

Rows: 1

Parsing a LocalTime:

Query
RETURN localtime('12:50:35.556') AS theLocalTime
Table 5. Result
theLocalTime

12:50:35.556

Rows: 1

Accessing components of temporal instants

Components of temporal instant values can be accessed as properties.

Table 6. Components of temporal instant values and where they are supported
Component Description Type Range/Format Date DateTime LocalDateTime Time LocalTime

instant.year

The year component represents the astronomical year number of the instant.[1]

Integer

At least 4 digits. For more information, see the rules for using the Year component

X

X

X

instant.quarter

The quarter-of-the-year component.

Integer

1 to 4

X

X

X

instant.month

The month-of-the-year component.

Integer

1 to 12

X

X

X

instant.week

The week-of-the-year component.[2]

Integer

1 to 53

X

X

X

instant.weekYear

The year that the week-of-year component belongs to.[3]

Integer

At least 4 digits. For more information, see the rules for using the Year component

X

X

X

instant.dayOfQuarter

The day-of-the-quarter component.

Integer

1 to 92

X

X

X

instant.quarterDay

The day-of-the-quarter component. (alias for instant.dayOfQuarter)

Integer

1 to 92

X

X

X

instant.day

The day-of-the-month component.

Integer

1 to 31

X

X

X

instant.ordinalDay

The day-of-the-year component.

Integer

1 to 366

X

X

X

instant.dayOfWeek

The day-of-the-week component (the first day of the week is Monday).

Integer

1 to 7

X

X

X

instant.weekDay

The day-of-the-week component (alias for instant.dayOfWeek).

Integer

1 to 7

X

X

X

instant.hour

The hour component.

Integer

0 to 23

X

X

X

X

instant.minute

The minute component.

Integer

0 to 59

X

X

X

X

instant.second

The second component.

Integer

0 to 59

X

X

X

X

instant.millisecond

The millisecond component.

Integer

0 to 999

X

X

X

X

instant.microsecond

The microsecond component.

Integer

0 to 999999

X

X

X

X

instant.nanosecond

The nanosecond component.

Integer

0 to 999999999

X

X

X

X

instant.timezone

The timezone component.

String

Depending on how the time zone was specified, this is either a time zone name or an offset from UTC in the format ±HHMM

X

X

instant.offset

The timezone offset.

String

±HHMM

X

X

instant.offsetMinutes

The timezone offset in minutes.

Integer

-1080 to +1080

X

X

instant.offsetSeconds

The timezone offset in seconds.

Integer

-64800 to +64800

X

X

instant.epochMillis

The number of milliseconds between 1970-01-01T00:00:00+0000 and the instant.[4]

Integer

Positive for instants after and negative for instants before 1970-01-01T00:00:00+0000

X

instant.epochSeconds

The number of seconds between 1970-01-01T00:00:00+0000 and the instant.[5]

Integer

Positive for instants after and negative for instants before 1970-01-01T00:00:00+0000

X

1. This is in accordance with the Gregorian calendar; i.e. years AD/CE start at year 1, and the year before that (year 1 BC/BCE) is 0, while year 2 BCE is -1 etc.

2. The first week of any year is the week that contains the first Thursday of the year, and thus always contains January 4.

3. For dates from December 29, this could be the next year, and for dates until January 3 this could be the previous year, depending on how week 1 begins.

4. The expression datetime().epochMillis returns the equivalent value of the timestamp() function.

5. For the nanosecond part of the epoch offset, the regular nanosecond component (instant.nanosecond) can be used.

The following query shows how to extract the components of a Date value:

Query
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
Table 7. Result
d.year d.quarter d.month d.week d.weekYear d.day d.ordinalDay d.dayOfWeek d.dayOfQuarter

1984

4

10

41

1984

11

285

4

11

Rows: 1

The following query shows how to extract the date related components of a DateTime value:

Query
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
Table 8. Result
d.year d.quarter d.month d.week d.weekYear d.day d.ordinalDay d.dayOfWeek d.dayOfQuarter

1984

4

11

45

1984

11

316

7

42

Rows: 1

The following query shows how to extract the time related components of a DateTime value:

Query
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
Table 9. Result
d.hour d.minute d.second d.millisecond d.microsecond d.nanosecond

12

31

14

645

645876

645876123

Rows: 1

The following query shows how to extract the epoch time and timezone related components of a DateTime value:

Query
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
Table 10. Result
d.timezone d.offset d.offsetMinutes d.epochSeconds d.epochMillis

"Europe/Stockholm"

"+01:00"

60

469020674

469020674645

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 which can be arbitrarily large.

    • The value of the last — and least significant — 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 with T, even when no components of the date part are given.

  • 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 LocalDateTime.

The following table lists the component identifiers for the unit-based form:

Component identifier Description Comments

Y

Years

M

Months

Must be specified before T

W

Weeks

D

Days

H

Hours

M

Minutes

Must be specified after T

S

Seconds

Examples

The following examples demonstrate various methods of parsing Duration values. For more details, refer to Creating a Duration from a string.

Return a Duration of 14 days, 16 hours and 12 minutes:

Query
RETURN duration('P14DT16H12M') AS theDuration
Table 11. Result
theDuration

P14DT16H12M

Rows: 1

Return a Duration of 5 months, 1 day and 12 hours:

Query
RETURN duration('P5M1.5D') AS theDuration
Table 12. Result
theDuration

P5M1DT12H

Rows: 1

Return a Duration of 45 seconds:

Query
RETURN duration('PT0.75M') AS theDuration
Table 13. Result
theDuration

PT45S

Rows: 1

Return a Duration of 2 weeks, 3 days and 12 hours:

Query
RETURN duration('P2.5W') AS theDuration
Table 14. Result
theDuration

P17DT12H

Rows: 1

Accessing 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

duration.years

The total number of years

Integer

Each set of 4 quarters is counted as 1 year; each set of 12 months is counted as 1 year.

duration.quarters

The total number of quarters

Integer

Each year is counted as 4 quarters; each set of 3 months is counted as 1 quarter.

duration.months

The total number of months

Integer

Each year is counted as 12 months; each_quarter_ is counted as 3 months.

Days

duration.weeks

The total number of weeks

Integer

Each set of 7 days is counted as 1 week.

duration.days

The total number of days

Integer

Each week is counted as 7 days.

Seconds

duration.hours

The total number of hours

Integer

Each set of 60 minutes is counted as 1 hour; each set of 3600 seconds is counted as 1 hour.

duration.minutes

The total number of minutes

Integer

Each hour is counted as 60 minutes; each set of 60 seconds is counted as 1 minute.

duration.seconds

The total number of seconds

Integer

Each hour is counted as 3600 seconds; each minute is counted as 60 seconds.

duration.milliseconds

The total number of milliseconds

Integer

Each set of 1000 milliseconds is counted as 1 second.

duration.microseconds

The total number of microseconds

Integer

Each millisecond is counted as 1000 microseconds.

duration.nanoseconds

The total number of nanoseconds

Integer

Each microsecond is counted as 1000 nanoseconds.

Please note that:

  • Cypher uses UTC-SLS when handling leap seconds.

  • There are not always 24 hours in 1 day; when switching to/from daylight savings time, a day can have 23 or 25 hours.

  • There are not always the same number of days in a month.

  • Due to leap years, there are not always the same number of days in a year.

It is also possible to access the smaller (less significant) components of a component group bounded by the largest (most significant) component of the group:

Component Component Group Description Type

duration.quartersOfYear

Months

The number of quarters in the group that do not make a whole year

Integer

duration.monthsOfYear

Months

The number of months in the group that do not make a whole year

Integer

duration.monthsOfQuarter

Months

The number of months in the group that do not make a whole quarter

Integer

duration.daysOfWeek

Days

The number of days in the group that do not make a whole week

Integer

duration.minutesOfHour

Seconds

The number of minutes in the group that do not make a whole hour

Integer

duration.secondsOfMinute

Seconds

The number of seconds in the group that do not make a whole minute

Integer

duration.millisecondsOfSecond

Seconds

The number of milliseconds in the group that do not make a whole second

Integer

duration.microsecondsOfSecond

Seconds

The number of microseconds in the group that do not make a whole second

Integer

duration.nanosecondsOfSecond

Seconds

The number of nanoseconds in the group that do not make a whole second

Integer

The following query shows how to extract the month based components of a Duration value:

Query
WITH duration({years: 1, months: 5, days: 111, minutes: 42}) AS d
RETURN d.years, d.quarters, d.quartersOfYear, d.months, d.monthsOfYear, d.monthsOfQuarter
Table 15. Result
d.years d.quarters d.quartersOfYear d.months d.monthsOfYear d.monthsOfQuarter

1

5

1

17

5

2

Rows: 1

The following query shows how to extract the day based components of a Duration value:

Query
WITH duration({months: 5, days: 25, hours: 1}) AS d
RETURN d.weeks, d.days, d.daysOfWeek
Table 16. Result
d.weeks d.days d.daysOfWeek

3

25

4

Rows: 1

The following query shows how to extract the most significant second based components of a Duration value:

Query
WITH 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
Table 17. Result
d.hours d.minutes d.seconds d.milliseconds d.microseconds d.nanoseconds

1

61

3661

3661111

3661111111

3661111111111

Rows: 1

The following query shows how to extract the less significant second based components of a Duration value:

Query
WITH 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
Table 18. Result
d.minutesOfHour d.secondsOfMinute d.millisecondsOfSecond d.microsecondsOfSecond d.nanosecondsOfSecond

1

1

111

111111

111111111

Rows: 1

Examples

The following examples illustrate the use of some of the temporal functions and operators. Refer to Temporal functions - instant types and Temporal operators for more details.

Create a Duration representing 1.5 days:

Query
RETURN duration({days: 1, hours: 12}) AS theDuration
Table 19. Result
theDuration

P1DT12H

Rows: 1

Compute the Duration between two temporal instants:

Query
RETURN duration.between(date('1984-10-11'), date('2015-06-24')) AS theDuration
Table 20. Result
theDuration

P30Y8M13D

Rows: 1

Compute the number of days between two Date values:

Query
RETURN duration.inDays(date('2014-10-11'), date('2015-08-06')) AS theDuration
Table 21. Result
theDuration

P299D

Rows: 1

Get the first Date of the current year:

Query
RETURN date.truncate('year') AS day
Table 22. Result
day

2022-01-01

Rows: 1

Get the Date of the Thursday in the week of a specific date:

Query
RETURN date.truncate('week', date('2019-10-01'), {dayOfWeek: 4}) AS thursday
Table 23. Result
thursday

2019-10-03

Rows: 1

Get the Date of the last day of the next month:

Query
RETURN date.truncate('month', date() + duration('P2M')) - duration('P1D') AS lastDay
Table 24. Result
lastDay

2022-09-30

Rows: 1

Add a Duration to a Date:

Query
RETURN time('13:42:19') + duration({days: 1, hours: 12}) AS theTime
Table 25. Result
theTime

01:42:19Z

Rows: 1

Add two Duration values:

Query
RETURN duration({days: 2, hours: 7}) + duration({months: 1, hours: 18}) AS theDuration
Table 26. Result
theDuration

P1M2DT25H

Rows: 1

Multiply a Duration by a number:

Query
RETURN duration({hours: 5, minutes: 21}) * 14 AS theDuration
Table 27. Result
theDuration

PT74H54M

Rows: 1

Divide a Duration by a number:

Query
RETURN duration({hours: 3, minutes: 16}) / 2 AS theDuration
Table 28. Result
theDuration

PT1H38M

Rows: 1

Examine whether two instants are less than one day apart:

Query
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
Table 29. Result
lessThanOneDayApart

true

Rows: 1

Return the abbreviated name of the current month:

Query
RETURN ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"][date().month-1] AS month
Table 30. Result
month

"Aug"

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.