Temporal Functions

DateTime

A DateTime represents an absolute moment at a specific offset from UTC. For example, May 21, 2002, at 2:30:00PM UTC-8, or July 30, 2039, at 12:00:00AM UTC. DateTimes with different offsets may refer to the same moment in time and are still distinct. For example, June 11, 4 PM UTC-8 is not the same DateTime as June 11, 7 PM UTC-5, even though they refer to the same moment in time (e.g., the same unix timestamp).

• datetime() - Returns the server’s current clock time in the server’s timezone.

{
    "columns": [
        "datetime()"
    ],
    "results": [
        [
            "2023-05-04T15:26:07.769696-05:00[America/Chicago]"
        ]
    ]
}

• datetime(datetime: string) - Expects an ISO-8601 extended offset date-time string. For example, 2011-12-03T10:15:30+01:00. The string may also include an optional timezone in square brackets, but the DateTime value created will track only the UTC offset, not the timezone name.

{
    "columns": [
        "datetime(\"2011-12-03T10:15:30+01:00\")"
    ],
    "results": [
        [
            "2011-12-03T10:15:30+01:00"
        ]
    ]
}

• datetime(datetime: string, format: string) - Expects a DateTime string parseable according to the format string. The format string is interpreted using Java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

{
    "columns": [
        "datetime(\"Wed, 1 May 2019 11:05:30 EST\", \"E, d MMM yyyy HH:mm:ss z\")"
    ],
    "results": [
        [
            "2019-05-01T11:05:30-04:00[America/New_York]"
        ]
    ]
}

• datetime(options: map) - Options may be any combination of the following. If empty, the map is ignored, and the behavior is the same as datetime()
• date: a localdatetime or datetime to use as a base date
  • When given a localdatetime: interpret the localdatetime as a datetime in the provided or default timezone (see below)
  • When given a datetime: interpret the datetime as a datetime in its original timezone, if no timezone is provided. If a timezone is provided, convert the provided datetime to the equivalent moment in the provided timezone
  • eg datetime({date: datetime("2011-12-03T10:15:30+01:00"), timezone: "EST"}) = datetime("2011-12-03T04:15:30-05:00")
  • When neither is provided, Jan 1 0000 is used
• timezone: a string representing a timezone whose offset should be used (ex: PST or America/Los_Angeles). If the timezone can’t be parsed to an offset, the offset 0 (UTC) will be used. If no timezone is provided and no date is provided, the server timezone will be used. The zone provided must be a valid Java ZoneId (which may vary depending on the JVM)
• year: integer representing the years since the base date
• quarter: integer (1-4) representing the quarter within the year
• month: integer (1-12) representing the month within the year
• week: integer (1-53) representing the week within the (week-based) year
• dayOfQuarter: integer (1-92) representing the day within the quarter
• day: integer (1-31) representing the day within the month
• ordinalDay: integer (1-366) representing the day within the year
• dayOfWeek: integer (1-7) representing the day within the week
• hour: integer (0-23) representing the hour within the day
• minute: integer (0-59) representing the minute within the hour
• second: integer (0-59) representing the second within the minute
• millisecond: integer (0-999) representing the millisecond within the second
• microsecond: integer (0-999,999) representing the microsecond within the second
• nanosecond: integer (0-999,999,999) representing the nanosecond within the second
• epochMillis: integer representing the number of milliseconds since the epoch
• epochSeconds: integer representing the number of seconds since the epoch

{
    "columns": [
        "d.year",
        "d.quarter",
        "d.month",
        "d.week",
        "d.weekYear",
        "d.day",
        "d.ordinalDay",
        "d.dayOfWeek",
        "d.dayOfQuarter"
    ],
    "results": [
        [
            1984,
            4,
            11,
            45,
            null,
            11,
            316,
            7,
            42
        ]
    ]
}

LocalDateTime

A LocalDateTime represents a date and time, but no specific offset from UTC. For example, August 2nd, 2081 at 3:00:00PM. Note that as they lack a UTC offset, LocalDateTimes do not represent a specific moment, so questions like “how many seconds was August 2nd, 2018 at 3PM after the epoch?” are ill-defined.

• localdatetime() - Returns the server’s current clock time in the server’s timezone

{
    "columns": [
        "localdatetime()"
    ],
    "results": [
        [
            "2023-05-05T09:21:30.872348"
        ]
    ]
}

• localdatetime(datetime: string) - Expects an ISO-8601 date-time string. For example, 2021-01-03T23:11:04.

{
    "columns": [
        "localdatetime(\"2021-01-03T23:11:04\")"
    ],
    "results": [
        [
            "2021-01-03T23:11:04"
        ]
    ]
}

• localdatetime(datetime: string, format: string) - Expects a datetime string parseable according to the format string. The format string is interpreted using java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

{
  "columns": [
    "localdatetime(\"Wed, 1 May 2019 11:05:30 EST\", \"E, d MMM yyyy HH:mm:ss z\")"
  ],
  "results": [
    [
      "2019-05-01T11:05:30"
    ]
  ]
}

• localdatetime(options: map). Options may be any combination of the following. If empty, the behavior is the same as localdatetime(). Alternatively, a timezone may be provided as the only field, in which case the server’s clock time in the provided timezone will be returned (or UTC if the timezone could not be parsed).
• date: a localdatetime or datetime to use as a base datetime
  • When given a datetime or localdatetime: interpret the datetime as a localdatetime by discarding any timezone component
  • When neither is provided, Jan 1 0000 is used
• year: integer representing the years since the base date
• quarter: integer (1-4) representing the quarter within the year
• month: integer (1-12) representing the month within the year
• week: integer (1-53) representing the week within the (week-based) year
• dayOfQuarter: integer (1-92) representing the day within the quarter
• day: integer (1-31) representing the day within the month
• ordinalDay: integer (1-366) representing the day within the year
• dayOfWeek: integer (1-7) representing the day within the week
• hour: integer (0-23) representing the hour within the day
• minute: integer (0-59) representing the minute within the hour
• second: integer (0-59) representing the second within the minute
• millisecond: integer (0-999) representing the millisecond within the second
• microsecond: integer (0-999,999) representing the microsecond within the second
• nanosecond: integer (0-999,999,999) representing the nanosecond within the second
• epochMillis: integer representing the number of milliseconds since the epoch
• epochSeconds: integer representing the number of seconds since the epoch

{
  "columns": [
    "localdatetime({ year: 1995, month: 4, day: 25, hour: 5, minute: 1, second: 53 })"
  ],
  "results": [
    [
      "1995-04-25T05:01:53"
    ]
  ]
}

Date

Date represents an unzoned date. Examples of dates are February 2, 2009; November 28, 1941. Dates do not contain any offset information.

• date() - Returns the server’s current date

{
  "columns": [
    "date()"
  ],
  "results": [
    [
      "2023-05-05T09:36:25.941144"
    ]
  ]
}

• date(date: string) - Expects an ISO-8601 date string. For example, ‘1986-06-07’

{
  "columns": [
    "date(\"1986-06-07\")"
  ],
  "results": [
    [
      "1986-06-07"
    ]
  ]
}

• date(date: string, format: string) - Expects a date string parseable according to the format string. The format string is interpreted using java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

{
  "columns": [
    "date(\"Wed, Jul 4, '01\", \"EEE, MMM d, ''yy\")"
  ],
  "results": [
    [
      "2001-07-04"
    ]
  ]
}

• date(options: map) - Options may be any combination of the following. All options modify a base date of Jan 1 0000
• year: integer representing the years since the base date
• quarter: integer (1-4) representing the quarter within the year
• month: integer (1-12) representing the month within the year
• week: integer (1-53) representing the week within the (week-based) year
• dayOfQuarter: integer (1-92) representing the day within the quarter
• day: integer (1-31) representing the day within the month
• ordinalDay: integer (1-366) representing the day within the year
• dayOfWeek: integer (1-7) representing the day within the week

{
  "columns": [
    "date({ year: 1995, month: 4, day: 24 })"
  ],
  "results": [
    [
      "1995-04-24"
    ]
  ]
}

Time

Time represents a time in at a specific offset from UTC. Examples of times are “1:27 AM UTC+1”, “4:05 PM UTC”, “11:22 PM UTC+9”. As with DateTime, multiple times may represent the same time within a day, but if they have different offsets, the time values are still considered distinct.

• time() - returns the server’s current clock time in the server’s timezone

{
  "columns": [
    "time()"
  ],
  "results": [
    [
      "09:47:05.660853"
    ]
  ]
}

• time(time: string) - Expects an ISO-8601 extended time-string. For example, 19:45:03. The offset portion will be truncated to a 15-minute interval.

{
  "columns": [
    "time(\"19:45:03\")"
  ],
  "results": [
    [
      "19:45:03"
    ]
  ]
}

• time(time: string, format: string) -  Expects a time string parseable according to the format string. The format string is interpreted using java’s syntax for time formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html

{
  "columns": [
    "time(\"Apr 1, 11 oclock in '19\", \"MMM d, HH 'oclock in '''yy\")"
  ],
  "results": [
    [
      "11:00"
    ]
  ]
}

• time(options: map) Options may be any combination of:
• hour: integer (0-23) representing the hour within the day
• minute: integer (0-59) representing the minute within the hour
• second: integer (0-59) representing the second within the minute
• millisecond: integer (0-999) representing the millisecond within the second
• microsecond: integer (0-999,999) representing the microsecond within the second
• nanosecond: integer (0-999,999,999) representing the nanosecond within the second
• offsetSeconds: integer representing the number of seconds (-64800 to 64800 in 15-minute/900-second increments) offset from UTC

{
  "columns": [
    "time({ hour: 10, minute: 4, second: 24, nanosecond: 110 })"
  ],
  "results": [
    [
      "10:04:24.000000110"
    ]
  ]
}

LocalTime

LocalTime represents a time at no specific offset from UTC. Examples include “3:00 PM”, “6:40 AM”, and “5:05 PM”. LocalTimes contain no offset information

• localtime() - returns the server’s current clock time

• localtime(time: string) - Expects an ISO-8601 extended time-string. For example, 12:45:03.

• localtime(time: string, format: string) -  Expects a time string parseable according to the format string. The format string is interpreted using java’s syntax for time formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html

• localtime(options: map) Options may be any combination of:

• hour: integer (0-23) representing the hour within the day
• minute: integer (0-59) representing the minute within the hour
• second: integer (0-59) representing the second within the minute
• millisecond: integer (0-999) representing the millisecond within the second
• microsecond: integer (0-999,999) representing the microsecond within the second
• nanosecond: integer (0-999,999,999) representing the nanosecond within the second

Duration

Duration represents a difference between 2 temporal values of the same type (either datetime or localdatetime) in the ISO-8601 duration format{ target="_blank" }. For example, the duration between May 3 2020 and May 8 2020 is 120 hours. The duration between February 28 2000 and March 1 2000 is 48 hours, while the duration between February 28 2001 and March 1 2001 is 24 hours. The duration between 1:30 PM UTC-1 (from a datetime) and 2:00 PM (at no offset, from a localdatetime) is undefined. As a final example, the duration between May 29 1986, 2PM PST and May 30, 1986 12AM UTC is 3 hours.

{
  "columns": [
    "duration({ days: 24 })"
  ],
  "results": [
    [
      "PT576H"
    ]
  ]
}

{
  "columns": [
    "duration.between(localdatetime({day: 3, month: 5, year:2020}), localdatetime({day: 8, month: 5, year:2020}))"
  ],
  "results": [
    [
      "PT120H"
    ]
  ]
}

{
  "columns": [
    "duration.between(datetime({day: 28, month: 2, year:2000}), datetime({day: 1, month: 3, year:2000}))"
  ],
  "results": [
    [
      "PT48H"
    ]
  ]
}

{
  "columns": [
    "duration.between(datetime({day: 28, month: 2, year:2001}), datetime({day: 1, month: 3, year:2001}))"
  ],
  "results": [
    [
      "PT24H"
    ]
  ]
}

{
  "columns": [
    "duration.between(datetime({day: 29, month: 5, hour: 14, year: 1986, timezone: \"PST\"}), datetime({day: 30, month: 5, hour: 0, year: 1986, timezone: \"UTC\"}))"
  ],
  "results": [
    [
      "PT3H"
    ]
  ]
}

Durations may also be directly constructed as the sum of individual components. The valid components of a duration are as follows:

• years
• quarters
• months
• weeks
• days
• hours
• minutes
• seconds
• milliseconds
• microseconds
• nanoseconds

Because temporal units at or above the scope of days are not fixed in length, an estimation of the duration in terms of smaller units is provided. For example, a duration of 1 day is estimated as 86400 seconds, even though some days have leap seconds. When an estimation is made, a message will be logged to warn the user. When directly constructing durations directly, each component must be an integer.

{
  "columns": [
    "duration({ days: 1 })"
  ],
  "results": [
    [
      "PT24H"
    ]
  ]
}

{
  "columns": [
    "duration({ hours: 25, minutes: 7, seconds: 20, milliseconds: 82 })"
  ],
  "results": [
    [
      "PT25H7M20.082S"
    ]
  ]
}

{
  "columns": [
    "duration({ years: 1 })"
  ],
  "results": [
    [
      "PT8765H49M12S"
    ]
  ]
}

Typically, durations are not used directly, but instead handled as a quantity of a familiar unit. In Cypher, this is represented using property-like syntax: Given a duration value named d, d.years, d.quarters, d.months, d.weeks, d.days, d.hours, d.minutes, d.seconds, d.milliseconds, d.microseconds, and d.nanoseconds are all valid expressions. When computing the length of a duration in terms of a unit, the result is rounded down to the nearest integer.

{
  "columns": [
    "duration({ seconds: 3, milliseconds: 500, microseconds: 700 }).milliseconds"
  ],
  "results": [
    [
      3501
    ]
  ]
}

{
  "columns": [
    "duration.between(datetime({day: 28, month: 2, year:2001}), datetime({day: 1, month: 3, year:2001})).days"
  ],
  "results": [
    [
      1
    ]
  ]
}