Date functions in AQL

Date and time representations

AQL offers functionality to work with dates, but it does not have a special data type for dates (neither does JSON, which is usually used as format to ship data into and out of ArangoDB). Instead, dates in AQL are represented by either numbers or strings.

All date function operations are done in the Unix time system. Unix time counts all non leap seconds beginning with January 1st 1970 00:00:00.000 UTC, also know as the Unix epoch. A point in time is called timestamp. A timestamp has the same value at every point on earth. The date functions use millisecond precision for timestamps.

Time unit definitions:

• millisecond: 1/1000 of a second
• second: one SI second 
• minute: one minute is defined as 60 seconds
• hour: one hour is defined as 60 minutes
• day: one day is defined as 24 hours
• week: one week is defined as 7 days
• month: one month is defined as 1/12 of a year
• year: one year is defined as 365.2425 days

All functions that require dates as arguments accept the following input values:

• numeric timestamps, millisecond precision.

  An example timestamp value is 1399472349522, which translates to 2014-05-07T14:19:09.522Z.

  Valid range: -62167219200000 .. 253402300799999 (inclusive)

• date time strings in ISO 8601  format:

  • YYYY-MM-DDTHH:MM:SS.MMM
  • YYYY-MM-DD HH:MM:SS.MMM
  • YYYY-MM-DD

  Milliseconds (.MMM) are always optional. Two digits for the hours (HH), minutes (MM) and seconds (SS) are mandatory, i.e. zero-padding is required for the values 0 through 9 (e.g. 05 instead of 5). Leading zeroes for the year (YYYY), month (MM) and day (DD) can be left out, but is discouraged.

  A time offset may optionally be added at the end of the string, with the hours and minutes that need to be added or subtracted to the date time value. For example, 2014-05-07T14:19:09+01:00 can be used to specify a one hour offset, and 2014-05-07T14:19:09+07:30 can be specified for seven and half hours offset. Negative offsets are also possible. Alternatively to an offset, a Z can be used to indicate UTC / Zulu time. An example value is 2014-05-07T14:19:09.522Z meaning May 7th 2014, 14:19:09 and 522 milliseconds, UTC / Zulu time. Another example value without time component is 2014-05-07Z.

  Valid range: "0000-01-01T00:00:00.000Z" .. "9999-12-31T23:59:59.999Z" (inclusive)

Any date/time values outside the valid range that are passed into an AQL date function makes the function return null and trigger a warning for the query, which can optionally be escalated to an error and abort the query. This also applies to operations which produce an invalid value.

DATE_HOUR( 2 * 60 * 60 * 1000 ) // 2
DATE_HOUR("1970-01-01T02:00:00") // 2

You are free to store age determinations of specimens, incomplete or fuzzy dates and the like in different, more appropriate ways of course. AQL’s date functions are most certainly not of any help for such dates, but you can still use language constructs like SORT (which also supports sorting of arrays) and indexes.

Current date and time

DATE_NOW()

DATE_NOW() → timestamp

Get the current unix time as numeric timestamp.

• returns timestamp (number): the current unix time as a timestamp. The return value has millisecond precision. To convert the return value to seconds, divide it by 1000.

Note that this function is evaluated on every invocation and may return different values when invoked multiple times in the same query. Assign it to a variable to use the exact same timestamp multiple times.

Conversion

DATE_TIMESTAMP() and DATE_ISO8601() can be used to convert ISO 8601 date time strings to numeric timestamps and numeric timestamps to ISO 8601 date time strings.

Both also support individual date components as separate function arguments, in the following order:

• year
• month
• day
• hour
• minute
• second
• millisecond

All components following the day are optional and can be omitted. Note that no time offset can be specified when using separate date components, and UTC / Zulu time is used.

The following calls to DATE_TIMESTAMP() are equivalent and all return 1399472349522:

DATE_TIMESTAMP("2014-05-07T14:19:09.522")
DATE_TIMESTAMP("2014-05-07T14:19:09.522Z")
DATE_TIMESTAMP("2014-05-07 14:19:09.522")
DATE_TIMESTAMP("2014-05-07 14:19:09.522Z")
DATE_TIMESTAMP(2014, 5, 7, 14, 19, 9, 522)
DATE_TIMESTAMP(1399472349522)

The same is true for calls to DATE_ISO8601() that also accepts variable input formats:

DATE_ISO8601("2014-05-07T14:19:09.522Z")
DATE_ISO8601("2014-05-07 14:19:09.522Z")
DATE_ISO8601(2014, 5, 7, 14, 19, 9, 522)
DATE_ISO8601(1399472349522)

The above functions are all equivalent and return "2014-05-07T14:19:09.522Z".

DATE_ISO8601()

DATE_ISO8601(date) → dateString

Return an ISO 8601 date time string from date. The date time string always uses UTC / Zulu time, indicated by the Z at its end.

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns dateString: date and time expressed according to ISO 8601, in Zulu time

DATE_ISO8601(year, month, day, hour, minute, second, millisecond) → dateString

Return a ISO 8601 date time string from date, but allows to specify the individual date components separately. All parameters after day are optional.

• year (number): typically in the range 0..9999, e.g. 2017
• month (number): 1..12 for January through December
• day (number): 1..31 (upper bound depends on number of days in month)
• hour (number, optional): 0..23
• minute (number, optional): 0..59
• second (number, optional): 0..59
• milliseconds (number, optional): 0..999
• returns dateString: date and time expressed according to ISO 8601, in Zulu time

DATE_TIMESTAMP()

DATE_TIMESTAMP(date) → timestamp

Create a timestamp value from date. The return value has millisecond precision. To convert the return value to seconds, divide it by 1000.

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns timestamp (number): numeric timestamp

DATE_TIMESTAMP(year, month, day, hour, minute, second, millisecond) → timestamp

Create a timestamp value, but allows to specify the individual date components separately. All parameters after day are optional.

• year (number): typically in the range 0..9999, e.g. 2017
• month (number): 1..12 for January through December
• day (number): 1..31 (upper bound depends on number of days in month)
• hour (number, optional): 0..23
• minute (number, optional): 0..59
• second (number, optional): 0..59
• milliseconds (number, optional): 0..999
• returns timestamp (number): numeric timestamp

Negative values are not allowed, result in null and cause a warning. Values greater than the upper range bound overflow to the larger components (e.g. an hour of 26 is automatically turned into an additional day and two hours):

DATE_TIMESTAMP(2016, 12, -1) // returns null and issues a warning
DATE_TIMESTAMP(2016, 2, 32) // returns 1456963200000, which is March 3rd, 2016
DATE_TIMESTAMP(1970, 1, 1, 26) // returns 93600000, which is January 2nd, 1970, at 2 a.m.

IS_DATESTRING()

IS_DATESTRING(value) → bool

Check if an arbitrary string is suitable for interpretation as date time string.

• value (string): an arbitrary string
• returns bool (bool): true if value is a string that can be used in a date function. This includes partial dates such as 2015 or 2015-10 and strings containing invalid dates such as 2015-02-31. The function returns false for all non-string values, even if some of them may be usable in date functions.

Processing

DATE_DAYOFWEEK()

DATE_DAYOFWEEK(date, timezone) → weekdayNumber

Return the weekday number of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns weekdayNumber (number): 0..6 as follows:
  • 0 – Sunday
  • 1 – Monday
  • 2 – Tuesday
  • 3 – Wednesday
  • 4 – Thursday
  • 5 – Friday
  • 6 – Saturday

Examples

The 29th of August in 2020 was a Saturday:

RETURN DATE_DAYOFWEEK("2020-08-29")

[ 
  6 
]

The Unix epoch began on the 1st of January 1970, which was a Thursday:

RETURN DATE_DAYOFWEEK(0)

[ 
  4 
]

At 11 PM UTC, it is already the next day in Germany (UTC+1 or UTC+2):

RETURN DATE_DAYOFWEEK("2023-03-25T23:00:00.000Z", "Europe/Berlin")

[ 
  0 
]

DATE_YEAR()

DATE_YEAR(date, timezone) → year

Return the year of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns year (number): the year part of date as a number

Examples

Extract the year from a date time string:

RETURN DATE_YEAR("2020-08-29")

[ 
  2020 
]

Extract the year from a Unix timestamp:

RETURN DATE_YEAR(0)

[ 
  1970 
]

Extract the year from a date time string, with a point in time one hour before New Year’s Day in UTC, which is already New Year in Germany:

RETURN DATE_YEAR("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  2024 
]

DATE_MONTH()

DATE_MONTH(date, timezone) → month

Return the month of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns month (number): the month part of date as a number

Examples

Extract the month from a date time string:

RETURN DATE_MONTH("2020-08-29")

[ 
  8 
]

Extract the month from a Unix timestamp:

RETURN DATE_MONTH(0)

[ 
  1 
]

Extract the month from a date time string, with a point in time one hour before the next month in UTC, which is already the next month in Germany:

 RETURN DATE_MONTH("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  1 
]

DATE_DAY()

DATE_DAY(date, timezone) → day

Return the day of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns day (number): the day part of date as a number

Examples

Extract the day from a date time string:

RETURN DATE_DAY("2020-08-29")

[ 
  29 
]

Extract the day from a Unix timestamp:

RETURN DATE_DAY(0)

[ 
  1 
]

Extract the day from a date time string, with a point in time one hour before the next day in UTC, which is already the next day in Germany:

RETURN DATE_DAY("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  1 
]

DATE_HOUR()

Return the hour of date.

DATE_HOUR(date, timezone) → hour

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns hour (number): the hour part of date as a number

Examples

Extract the hour of a date time string:

RETURN DATE_HOUR("2020-08-29T16:30:05.123")

[ 
  16 
]

Extract the hour of a Unix timestamp:

RETURN DATE_HOUR(14400000)

[ 
  4 
]

Extract the hour of a date time string, using Germany’s timezone that is one hour ahead compared to UTC in this case:

RETURN DATE_HOUR("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  0 
]

DATE_MINUTE()

DATE_MINUTE(date, timezone) → minute

Return the minute of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns minute (number): the minute part of date as a number

Examples

Extract the minute of a date time string:

RETURN DATE_MINUTE("2020-08-29T16:30:05.123")

[ 
  30 
]

Extract the minute of a Unix timestamp:

RETURN DATE_MINUTE(2520000)

[ 
  42 
]

Extract the minute of a date time string, using Nepal’s timezone that is 5 hours and 45 minutes ahead of UTC:

RETURN DATE_MINUTE("2023-12-31T23:00:00.000Z", "Asia/Kathmandu")

[ 
  45 
]

DATE_SECOND()

DATE_SECOND(date) → second

Return the second of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns second (number): the seconds part of date as a number

Examples

Extract the second of a date time string:

RETURN DATE_SECOND("2020-08-29T16:30:05.123")

[ 
  5 
]

Extract the second of a Unix timestamp:

RETURN DATE_SECOND(1234567890)

[ 
  7 
]

DATE_MILLISECOND()

DATE_MILLISECOND(date) → millisecond

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns millisecond (number): the milliseconds part of date as a number

Examples

RETURN DATE_MILLISECOND("2020-08-29T16:30:05.123")

[ 
  123 
]

Extract the millisecond of a Unix timestamp:

RETURN DATE_MILLISECOND(1234567890)

[ 
  890 
]

DATE_DAYOFYEAR()

DATE_DAYOFYEAR(date, timezone) → dayOfYear

Return the day of year of date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns dayOfYear (number): the day of year number of date. The return values range from 1 to 365, or 366 in a leap year respectively.

Examples

Extract the day of year from a date time string:

RETURN DATE_DAYOFYEAR("2020-08-29")

[ 
  242 
]

Extract the day of year from a Unix timestamp:

RETURN DATE_DAYOFYEAR(86400000)

[ 
  2 
]

Determine the day of year from a date time string, with a point in time one hour before the next day in UTC, which is already the next day in Germany:

RETURN DATE_DAYOFYEAR("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  1 
]

DATE_ISOWEEK()

DATE_ISOWEEK(date) → weekDate

Return the week number in the year of date according to ISO 8601.

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns weekDate (number): the ISO week number of date. The return values range from 1 to 53. Monday is considered the first day of the week. There are no fractional weeks, thus the last days in December may belong to the first week of the next year, and the first days in January may be part of the previous year’s last week.

Examples

Determine the week number from a date time string:

RETURN DATE_ISOWEEK("2020-08-29")

[ 
  35 
]

Determine the week number from a Unix timestamp:

RETURN DATE_ISOWEEK(1234567890)

[ 
  3 
]

DATE_ISOWEEKYEAR()

DATE_ISOWEEKYEAR(date) → weekAndYear

Return the week number of date according to ISO 8601 and the year the week belongs to.

• date (number|string): numeric timestamp or ISO 8601 date time string
• returns weekAndYear (object): an object with two attributes
  • week (number): the ISO week number of date. The values range from 1 to 53. Monday is considered the first day of the week. There are no fractional weeks, thus the last days in December may belong to the first week of the next year, and the first days in January may be part of the previous year’s last week.
  • year (number): the year to which the ISO week number belongs to

Examples

January 1st of 2023 is part of the previous year’s last week:

RETURN DATE_ISOWEEKYEAR("2023-01-01")

[ 
  { 
    "week" : 52, 
    "year" : 2022 
  } 
]

The last two days of 2019 are part of the next year’s first week:

RETURN DATE_ISOWEEKYEAR("2019-12-30")

[ 
  { 
    "week" : 1, 
    "year" : 2020 
  } 
]

DATE_LEAPYEAR()

DATE_LEAPYEAR(date, timezone) → leapYear

Return whether date is in a leap year.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns leapYear (bool): true if date is in a leap year, false otherwise

Examples

2020 was a leap year:

RETURN DATE_LEAPYEAR("2020-01-01")

[ 
  true 
]

2021 was not a leap year:

RETURN DATE_LEAPYEAR("2021-01-01")

[ 
  false 
]

2016 was a leap year but the point in time is one hour before the next year in UTC, which is already 2017 in Germany, and that was not a leap year:

RETURN DATE_LEAPYEAR("2016-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  false 
]

DATE_QUARTER()

DATE_QUARTER(date, timezone) → quarter

Return which quarter date belongs to.

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns quarter (number): the quarter of the given date (1-based):
  • 1 – January, February, March
  • 2 – April, May, June
  • 3 – July, August, September
  • 4 – October, November, December

Examples

Determine the quarter of a date time string:

RETURN DATE_QUARTER("2020-08-29")

[ 
  3 
]

Determine the quarter of a date time string, with a point in time one hour before the next quarter in UTC, which is already the next quarter in Germany:

RETURN DATE_QUARTER("2023-12-31T23:00:00.000Z", "Europe/Berlin")

[ 
  1 
]

DATE_DAYS_IN_MONTH()

Return the number of days in the month of date.

DATE_DAYS_IN_MONTH(date, timezone) → daysInMonth

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns daysInMonth (number): the number of days in date’s month (28..31)

Examples

Determine the number of days in August using a date time string:

RETURN DATE_DAYS_IN_MONTH("2020-08-01")

[ 
  31 
]

Determine the number of days in September using a date time string:

RETURN DATE_DAYS_IN_MONTH("2020-09-01")

[ 
  30 
]

Determine the number of days in February in a leap year using a date time string:

RETURN DATE_DAYS_IN_MONTH("2020-02-01")

[ 
  29 
]

Determine the number of days in February in a a non-leap year using a date time string:

RETURN DATE_DAYS_IN_MONTH("2021-02-01")

[ 
  28 
]

Determine the number of days in the month using a Unix timestamp:

RETURN DATE_DAYS_IN_MONTH(3045600000)

[ 
  28 
]

Determine the number of days in the month using a date time string, with a point in time one hour before the end of November in UTC, which is already December in Germany:

RETURN DATE_DAYS_IN_MONTH("2023-11-30T23:00:00.000Z", "Europe/Berlin")

[ 
  31 
]

DATE_TRUNC()

DATE_TRUNC(date, unit, timezone) → isoDate

Truncates the given date after unit and returns the modified date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• unit (string): either of the following to specify the time unit (case-insensitive):
  • "y", "year", "years"
  • "m", "month", "months"
  • "d", "day", "days"
  • "h", "hour", "hours"
  • "i", "minute", "minutes"
  • "s", "second", "seconds"
  • "f", "millisecond", "milliseconds"
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the truncated ISO 8601 date time string

Examples

DATE_TRUNC('2017-02-03', 'month') // 2017-02-01T00:00:00.000Z
DATE_TRUNC('2017-02-03 04:05:06', 'hours') // 2017-02-03 04:00:00.000Z
DATE_TRUNC('2023-03-25 23:00:00', 'day') // 2023-03-25T00:00:00.000Z
DATE_TRUNC('2023-03-25 23:00:00', 'day', 'Europe/Berlin') // 2023-03-25T23:00:00.000Z

Truncate date time strings comprised of a year, month, and day to the year and group another attribute by it:

RETURN MERGE(
  FOR doc IN @data
    COLLECT q = DATE_TRUNC(doc.date, "year") INTO bucket
    RETURN { [DATE_YEAR(q)]: bucket[*].doc.value }
)

{
  "data": [
    {
      "date": "2018-03-05",
      "value": "Spring"
    },
    {
      "date": "2018-07-11",
      "value": "Summer"
    },
    {
      "date": "2018-10-26",
      "value": "Autumn"
    },
    {
      "date": "2019-01-09",
      "value": "Winter"
    },
    {
      "date": "2019-04-02",
      "value": "Spring"
    }
  ]
}

[ 
  { 
    "2018" : [ 
      "Spring", 
      "Summer", 
      "Autumn" 
    ], 
    "2019" : [ 
      "Winter", 
      "Spring" 
    ] 
  } 
]

DATE_ROUND()

DATE_ROUND(date, amount, unit, timezone) → isoDate

Bin a date/time into a set of equal-distance buckets, to be used for grouping.

• date (string|number): a date string or timestamp
• amount (number): number of units. Must be a positive integer value.
• unit (string): either of the following to specify the time unit (case-insensitive):
  • "d", "day", "days"
  • "h", "hour", "hours"
  • "i", "minute", "minutes"
  • "s", "second", "seconds"
  • "f", "millisecond", "milliseconds"
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the rounded ISO 8601 date time string

Examples

DATE_ROUND('2000-04-28T11:11:11.111Z', 1, 'day') // 2000-04-28T00:00:00.000Z
DATE_ROUND('2000-04-10T11:39:29Z', 15, 'minutes') // 2000-04-10T11:30:00.000Z
DATE_ROUND('2023-03-25T23:55:55.555Z', 1, 'day') // 2023-03-25T00:00:00.000Z
DATE_ROUND('2023-03-25T23:55:55.555Z', 1, 'day', "Europe/Berlin") // 2023-03-25T23:00:00.000Z

Round full date time strings to 5 minutes and aggregate temperature readings by these time buckets:

FOR doc IN @sensorData
  COLLECT
    date = DATE_ROUND(doc.timestamp, 5, "minutes")
  AGGREGATE
    count = COUNT(1),
    avg = AVG(doc.temp),
    min = MIN(doc.temp),
    max = MAX(doc.temp)
  RETURN { date, count, avg, min, max }

{
  "sensorData": [
    {
      "temp": 20.6,
      "timestamp": "2019-12-04T21:17:52.583Z"
    },
    {
      "temp": 20.2,
      "timestamp": "2019-12-04T21:19:53.516Z"
    },
    {
      "temp": 19.9,
      "timestamp": "2019-12-04T21:21:53.610Z"
    },
    {
      "temp": 19.8,
      "timestamp": "2019-12-04T21:23:52.522Z"
    },
    {
      "temp": 19.8,
      "timestamp": "2019-12-04T21:25:52.988Z"
    },
    {
      "temp": 19.7,
      "timestamp": "2019-12-04T21:27:54.005Z"
    }
  ]
}

[ 
  { 
    "date" : "2019-12-04T21:15:00.000Z", 
    "count" : 2, 
    "avg" : 20.4, 
    "min" : 20.2, 
    "max" : 20.6 
  }, 
  { 
    "date" : "2019-12-04T21:20:00.000Z", 
    "count" : 2, 
    "avg" : 19.85, 
    "min" : 19.8, 
    "max" : 19.9 
  }, 
  { 
    "date" : "2019-12-04T21:25:00.000Z", 
    "count" : 2, 
    "avg" : 19.75, 
    "min" : 19.7, 
    "max" : 19.8 
  } 
]

DATE_FORMAT()

DATE_FORMAT(date, format, timezone) → str

Format a date according to the given format string.

• date (string|number): a date string or timestamp
• format (string): a format string, see below
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns str (string): a formatted date string

The format parameter supports the following placeholders (case-insensitive):

• %t – timestamp, in milliseconds since midnight 1970-01-01
• %z – ISO date (0000-00-00T00:00:00.000Z)
• %w – day of week (0..6)
• %y – year (0..9999)
• %yy – year (00..99), abbreviated (last two digits)
• %yyyy – year (0000..9999), padded to length of 4
• %yyyyyy – year (-009999 .. +009999), with sign prefix and padded to length of 6
• %m – month (1..12)
• %mm – month (01..12), padded to length of 2
• %d – day (1..31)
• %dd – day (01..31), padded to length of 2
• %h – hour (0..23)
• %hh – hour (00..23), padded to length of 2
• %i – minute (0..59)
• %ii – minute (00..59), padded to length of 2
• %s – second (0..59)
• %ss – second (00..59), padded to length of 2
• %f – millisecond (0..999)
• %fff – millisecond (000..999), padded to length of 3
• %x – day of year (1..366)
• %xxx – day of year (001..366), padded to length of 3
• %k – ISO week number of year (1..53)
• %kk – ISO week number of year (01..53), padded to length of 2
• %l – leap year (0 or 1)
• %q – quarter (1..4)
• %a – days in month (28..31)
• %mmm – abbreviated English name of month (Jan..Dec)
• %mmmm – English name of month (January..December)
• %www – abbreviated English name of weekday (Sun..Sat)
• %wwww – English name of weekday (Sunday..Saturday)
• %& – special escape sequence for rare occasions
• %% – literal %
• % – ignored

%yyyy does not enforce a length of 4 for years before 0 and past 9999. The same format as for %yyyyyy is used instead. %yy preserves the sign for negative years and may thus return 3 characters in total.

Single % characters are ignored. Use %% for a literal %. To resolve ambiguities like in %mmonth (unpadded month number + the string month) between %mm + onth and %m + month, use the escape sequence %&: %m%&month.

Note that DATE_FORMAT() is a rather costly operation and may not be suitable for large datasets (like over 1 million dates). If possible, avoid formatting dates on server-side and leave it up to the client to do so. This function should only be used for special date comparisons or to store the formatted dates in the database. For better performance, use the primitive DATE_*() functions together with CONCAT() if possible.

Examples

DATE_FORMAT(DATE_NOW(), "%q/%yyyy") // quarter and year (e.g. "3/2015")
DATE_FORMAT(DATE_NOW(), "%dd.%mm.%yyyy %hh:%ii:%ss,%fff") // e.g. "18.09.2015 15:30:49,374"
DATE_FORMAT("1969", "Summer of '%yy") // "Summer of '69"
DATE_FORMAT("2016", "%%l = %l") // "%l = 1" (2016 is a leap year)
DATE_FORMAT("2016-03-01", "%xxx%") // "063", trailing % ignored

Show example calls of the formatting function and their results:

FOR format IN @formats
  RETURN CONCAT(
    format.equalTo,
    "('",
    format.date,
    "') = ",
    DATE_FORMAT(format.date, format.placeholder)
  )

{
  "formats": [
    {
      "date": "2023-03-25T23:00:00.000Z",
      "equalTo": "DATE_DAYOFWEEK",
      "placeholder": "%w"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_YEAR",
      "placeholder": "%yyyy"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_MONTH",
      "placeholder": "%m"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_DAY",
      "placeholder": "%d"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_HOUR",
      "placeholder": "%h"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_MINUTE",
      "placeholder": "%i"
    },
    {
      "date": "2023-12-31T23:00:23.000Z",
      "equalTo": "DATE_SECOND",
      "placeholder": "%s"
    },
    {
      "date": "2023-12-31T23:00:00.031Z",
      "equalTo": "DATE_MILLISECOND",
      "placeholder": "%f"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_DAYOFYEAR",
      "placeholder": "%x"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_ISOWEEK",
      "placeholder": "%k"
    },
    {
      "date": "2016-12-31T23:00:00.000Z",
      "equalTo": "DATE_LEAPYEAR",
      "placeholder": "%l"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_QUARTER",
      "placeholder": "%q"
    },
    {
      "date": "2023-11-30T23:00:00.000Z",
      "equalTo": "DATE_DAYS_IN_MONTH",
      "placeholder": "%a"
    },
    {
      "date": "2023-11-30T23:00:00.000Z",
      "equalTo": "DATE_TIMESTAMP",
      "placeholder": "%t"
    }
  ]
}

[ 
  "DATE_DAYOFWEEK('2023-03-25T23:00:00.000Z') = 6", 
  "DATE_YEAR('2023-12-31T23:00:00.000Z') = 2023", 
  "DATE_MONTH('2023-12-31T23:00:00.000Z') = 12", 
  "DATE_DAY('2023-12-31T23:00:00.000Z') = 31", 
  "DATE_HOUR('2023-12-31T23:00:00.000Z') = 23", 
  "DATE_MINUTE('2023-12-31T23:00:00.000Z') = 0", 
  "DATE_SECOND('2023-12-31T23:00:23.000Z') = 23", 
  "DATE_MILLISECOND('2023-12-31T23:00:00.031Z') = 31", 
  "DATE_DAYOFYEAR('2023-12-31T23:00:00.000Z') = 365", 
  "DATE_ISOWEEK('2023-12-31T23:00:00.000Z') = 52", 
  "DATE_LEAPYEAR('2016-12-31T23:00:00.000Z') = 1", 
  "DATE_QUARTER('2023-12-31T23:00:00.000Z') = 4", 
  "DATE_DAYS_IN_MONTH('2023-11-30T23:00:00.000Z') = 30", 
  "DATE_TIMESTAMP('2023-11-30T23:00:00.000Z') = 1701385200000" 
]

Show example calls of the formatting function and their results, using the timezone of Germany (and Nepal in one case):

FOR format IN @formats
  RETURN CONCAT(
    format.equalTo,
    "('",
    format.date,
    "') = ",
    DATE_FORMAT(format.date, format.placeholder, format.timezone)
  )

{
  "formats": [
    {
      "date": "2023-03-25T23:00:00.000Z",
      "equalTo": "DATE_DAYOFWEEK",
      "placeholder": "%w",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_YEAR",
      "placeholder": "%yyyy",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_MONTH",
      "placeholder": "%m",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_DAY",
      "placeholder": "%d",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_HOUR",
      "placeholder": "%h",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_MINUTE",
      "placeholder": "%i",
      "timezone": "Asia/Kathmandu"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_DAYOFYEAR",
      "placeholder": "%x",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_ISOWEEK",
      "placeholder": "%k",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2016-12-31T23:00:00.000Z",
      "equalTo": "DATE_LEAPYEAR",
      "placeholder": "%l",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-12-31T23:00:00.000Z",
      "equalTo": "DATE_QUARTER",
      "placeholder": "%q",
      "timezone": "Europe/Berlin"
    },
    {
      "date": "2023-11-30T23:00:00.000Z",
      "equalTo": "DATE_DAYS_IN_MONTH",
      "placeholder": "%a",
      "timezone": "Europe/Berlin"
    }
  ]
}

[ 
  "DATE_DAYOFWEEK('2023-03-25T23:00:00.000Z') = 0", 
  "DATE_YEAR('2023-12-31T23:00:00.000Z') = 2024", 
  "DATE_MONTH('2023-12-31T23:00:00.000Z') = 1", 
  "DATE_DAY('2023-12-31T23:00:00.000Z') = 1", 
  "DATE_HOUR('2023-12-31T23:00:00.000Z') = 0", 
  "DATE_MINUTE('2023-12-31T23:00:00.000Z') = 45", 
  "DATE_DAYOFYEAR('2023-12-31T23:00:00.000Z') = 1", 
  "DATE_ISOWEEK('2023-12-31T23:00:00.000Z') = 1", 
  "DATE_LEAPYEAR('2016-12-31T23:00:00.000Z') = 0", 
  "DATE_QUARTER('2023-12-31T23:00:00.000Z') = 1", 
  "DATE_DAYS_IN_MONTH('2023-11-30T23:00:00.000Z') = 31" 
]

Comparison and calculation

DATE_ADD()

DATE_ADD(date, amount, unit, timezone) → isoDate

Add amount given in unit to date and return the calculated date.

• date (number|string): numeric timestamp or ISO 8601 date time string
• amount (number|string): number of units to add (positive value) or subtract (negative value). It is recommended to use positive values only, and use DATE_SUBTRACT() for subtractions instead.
• unit (string): either of the following to specify the time unit to add or subtract (case-insensitive):
  • "y", "year", "years"
  • "m", "month", "months"
  • "w", "week", "weeks"
  • "d", "day", "days"
  • "h", "hour", "hours"
  • "i", "minute", "minutes"
  • "s", "second", "seconds"
  • "f", "millisecond", "milliseconds"
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the calculated ISO 8601 date time string

DATE_ADD(DATE_NOW(), -1, "day") // yesterday; also see DATE_SUBTRACT()
DATE_ADD(DATE_NOW(), 3, "months") // in three months
DATE_ADD(DATE_ADD("2015-04-01", 5, "years"), 1, "month") // May 1st 2020
DATE_ADD("2015-04-01", 12*5 + 1, "months") // also May 1st 2020
DATE_ADD(DATE_TIMESTAMP(DATE_YEAR(DATE_NOW()), 12, 24), -4, "years") // Christmas four years ago
DATE_ADD(DATE_ADD("2016-02", "month", 1), -1, "day") // last day of February (29th, because 2016 is a leap year!)

UTC does not have daylight saving time but Germany does, only adding 23 hours when adding a day:

RETURN {
  UTC: DATE_ADD("2023-03-25T23:00:00.000Z", 1, "day"),
  Germany: DATE_ADD("2023-03-25T23:00:00.000Z", 1, "day", "Europe/Berlin")
}

[ 
  { 
    "UTC" : "2023-03-26T23:00:00.000Z", 
    "Germany" : "2023-03-26T22:00:00.000Z" 
  } 
]

DATE_ADD(date, isoDuration, timezone) → isoDate

You may also pass an ISO duration string as amount and leave out unit.

• date (number|string): numeric timestamp or ISO 8601 date time string
• isoDuration (string): an ISO 8601 duration string to add to date, see below
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the calculated ISO 8601 date time string

The format is P_Y_M_W_DT_H_M_._S, where underscores stand for digits and letters for time intervals - except for the separators P (period) and T (time). The meaning of the other letters are:

• Y – years
• M – months (if before T)
• W – weeks
• D – days
• H – hours
• M – minutes (if after T)
• S – seconds (optionally with 3 decimal places for milliseconds)

The string must be prefixed by a P. A separating T is only required if H, M and/or S are specified. You only need to specify the needed pairs of letters and numbers.

DATE_ADD(DATE_NOW(), "P1Y") // add 1 year
DATE_ADD(DATE_NOW(), "P3M2W") // add 3 months and 2 weeks
DATE_ADD(DATE_NOW(), "P5DT26H") // add 5 days and 26 hours (=6 days and 2 hours)
DATE_ADD("2000-01-01", "PT4H") // add 4 hours
DATE_ADD("2000-01-01", "PT30M44.4S") // add 30 minutes, 44 seconds and 400 ms
DATE_ADD("2000-01-01", "P1Y2M3W4DT5H6M7.89S") // add a bit of everything

DATE_SUBTRACT()

DATE_SUBTRACT(date, amount, unit, timezone) → isoDate

Subtract amount given in unit from date and return the calculated date.

It works the same as DATE_ADD(), except that it subtracts. It is equivalent to calling DATE_ADD() with a negative amount, except that DATE_SUBTRACT() can also subtract ISO durations. Note that negative ISO durations are not supported (i.e. starting with -P, like -P1Y).

• date (number|string): numeric timestamp or ISO 8601 date time string
• amount (number|string): number of units to subtract (positive value) or add (negative value). It is recommended to use positive values only, and use DATE_ADD() for additions instead.
• unit (string): either of the following to specify the time unit to add or subtract (case-insensitive):
  • "y", "year", "years"
  • "m", "month", "months"
  • "w", "week", "weeks"
  • "d", "day", "days"
  • "h", "hour", "hours"
  • "i", "minute", "minutes"
  • "s", "second", "seconds"
  • "f", "millisecond", "milliseconds"
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the calculated ISO 8601 date time string

DATE_SUBTRACT(date, isoDuration, timezone) → isoDate

You may also pass an ISO duration string as amount and leave out unit.

• date (number|string): numeric timestamp or ISO 8601 date time string
• isoDuration (string): an ISO 8601 duration string to subtract from date, see below
• timezone (string, optional): if set, the date is assumed to be in the specified timezone, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns isoDate (string): the calculated ISO 8601 date time string

The format is P_Y_M_W_DT_H_M_._S, where underscores stand for digits and letters for time intervals - except for the separators P (period) and T (time). The meaning of the other letters are:

• Y – years
• M – months (if before T)
• W – weeks
• D – days
• H – hours
• M – minutes (if after T)
• S – seconds (optionally with 3 decimal places for milliseconds)

The string must be prefixed by a P. A separating T is only required if H, M and/or S are specified. You only need to specify the needed pairs of letters and numbers.

DATE_SUBTRACT(DATE_NOW(), 1, "day") // yesterday
DATE_SUBTRACT(DATE_TIMESTAMP(DATE_YEAR(DATE_NOW()), 12, 24), 4, "years") // Christmas four years ago
DATE_SUBTRACT(DATE_ADD("2016-02", "month", 1), 1, "day") // last day of February (29th, because 2016 is a leap year!)
DATE_SUBTRACT(DATE_NOW(), "P4D") // four days ago
DATE_SUBTRACT(DATE_NOW(), "PT1H3M") // 1 hour and 30 minutes ago

DATE_DIFF()

DATE_DIFF(date1, date2, unit, asFloat, timezone1, timezone2) → diff

Calculate the difference between two dates in given time unit, optionally with decimal places.

• date1 (number|string): numeric timestamp or ISO 8601 date time string
• date2 (number|string): numeric timestamp or ISO 8601 date time string
• unit (string): either of the following to specify the time unit to return the difference in (case-insensitive):
  • "y", "year", "years"
  • "m", "month", "months"
  • "w", "week", "weeks"
  • "d", "day", "days"
  • "h", "hour", "hours"
  • "i", "minute", "minutes"
  • "s", "second", "seconds"
  • "f", "millisecond", "milliseconds"
• asFloat (boolean, optional): if set to true, decimal places are preserved in the result. The default is false and an integer is returned.
• timezone1 (string, optional): if set, date1 is assumed to be in the specified timezone. If timezone2 is not set, then both date1 and date2 are assumed to be in the timezone specified by timezone1, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• timezone2 (string, optional): if set, date2 is assumed to be in the timezone specified by timezone2, and date1 is assumed to be in the timezone specified by timezone1, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns diff (number): the calculated difference as number in unit. The value is negative if date2 is before date1.

Determine how many days it is from New Year’s Eve until April Fools’ day:

RETURN DATE_DIFF("2023-12-01", "2024-04-01", "days")

[ 
  122 
]

Determine how many hours (with decimal places) Nepal and the USA are apart using the same date time string:

LET date = "2024-01-01T00:00:00.000Z"
RETURN DATE_DIFF(date, date, "hours", true, "Asia/Kathmandu", "America/Los_Angeles")

[ 
  -13.75 
]

Determine the hour difference between two date time strings, where Germany has one hour more due to the switch from winter to summer time:

RETURN {
  UTC: DATE_DIFF("2023-03-25T23:00:00.000Z", "2023-03-26T23:00:00.000Z", "hours"),
  Germany: DATE_DIFF("2023-03-25T23:00:00.000Z", "2023-03-26T23:00:00.000Z", "hours", "Europe/Berlin")
}

[ 
  { 
    "UTC" : 24, 
    "Germany" : 25 
  } 
]

DATE_COMPARE()

DATE_COMPARE(date1, date2, unitRangeStart, unitRangeEnd, timezone1, timezone2) → bool

Check if two partial dates match.

• date1 (number|string): numeric timestamp or ISO 8601 date time string
• date2 (number|string): numeric timestamp or ISO 8601 date time string
• unitRangeStart (string): unit to start from, see below
• unitRangeEnd (string, optional): unit to end with, leave out to only compare the component as specified by unitRangeStart. An error is raised if unitRangeEnd is a unit before unitRangeStart.
• timezone1 (string, optional): if set, date1 is assumed to be in the specified timezone. If timezone2 is not set, then both date1 and date2 are assumed to be in the timezone specified by timezone1, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• timezone2 (string, optional): if set, date2 is assumed to be in the timezone specified by timezone2, and date1 is assumed to be in the timezone specified by timezone1, e.g. "America/New_York", "Europe/Berlin", or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). See IANA timezone names . Throws an error if the timezone is not known to ArangoDB.
• returns bool (bool): true if the dates match, false otherwise

The parts to compare are defined by a range of time units. The full range is: years, months, days, hours, minutes, seconds, milliseconds (in this order).

All components of date1 and date2 as specified by the range are compared. You can refer to the units as:

• "y", "year", "years"
• "m", "month", "months"
• "d", "day", "days"
• "h", "hour", "hours"
• "i", "minute", "minutes"
• "s", "second", "seconds"
• "f", "millisecond", "milliseconds"

Examples

// Compare months and days, true on birthdays if you're born on 4th of April
DATE_COMPARE("1985-04-04", DATE_NOW(), "months", "days")

// Only matches on one day if the current year is a leap year!
// You may want to add or subtract one day from date1 to match every year.
DATE_COMPARE("1984-02-29", DATE_NOW(), "months", "days")

// compare years, months and days (true, because it's the same day)
DATE_COMPARE("2001-01-01T15:30:45.678Z", "2001-01-01T08:08:08.008Z", "years", "days")

You can directly compare ISO date strings if you want to find dates before or after a certain date, or in between two dates (>=, >, <, <=). No special date function is required. Equality tests (== and !=) only match the exact same date and time, however. You may use SUBSTRING() to compare partial date strings, DATE_COMPARE() is basically a convenience function for that. However, neither is really required to limit a search to a certain day as demonstrated here:

FOR doc IN coll
    FILTER doc.date >= "2015-05-15" AND doc.date < "2015-05-16"
    RETURN doc

Every ISO date on that day is greater than or equal to 2015-05-15 in a string comparison (e.g. 2015-05-15T11:30:00.000Z). Dates before 2015-05-15 are smaller and therefore filtered out by the first condition. Every date past 2015-05-15 is greater than this date in a string comparison, and therefore filtered out by the second condition. The result is that the time components in the dates you compare with are “ignored”. The query returns every document with date ranging from 2015-05-15T00:00:00.000Z to 2015-05-15T23:99:99.999Z. It would also include 2015-05-15T24:00:00.000Z, but that date is actually 2015-05-16T00:00:00.000Z and can only occur if inserted manually (you may want to pass dates through DATE_ISO8601() to ensure a correct date representation).

Leap days in leap years (29th of February) must be always handled manually, if you require so (e.g. birthday checks):

LET today = DATE_NOW()
LET noLeapYear = NOT DATE_LEAPYEAR(today)

FOR user IN users
    LET birthday = noLeapYear AND
                   DATE_MONTH(user.birthday) == 2 AND
                   DATE_DAY(user.birthday) == 29
                   ? DATE_SUBTRACT(user.birthday, 1, "day") /* treat like 28th in non-leap years */
                   : user.birthday
    FILTER DATE_COMPARE(today, birthday, "month", "day")
    /* includes leaplings on the 28th of February in non-leap years,
     * but excludes them in leap years which do have a 29th February.
     * Replace DATE_SUBTRACT() by DATE_ADD() to include them on the 1st of March
     * in non-leap years instead (depends on local jurisdiction).
     */
    RETURN user

Compare the time of two date time strings that is different in UTC but the same in two different local timezones:

RETURN {
  Germany: DATE_UTCTOLOCAL("2023-04-01T09:00:00.000Z", "Europe/Berlin"),
  USA: DATE_UTCTOLOCAL("2024-04-01T18:00:00.000Z", "America/Los_Angeles"),
  sameTimeUTC: DATE_COMPARE("2023-04-01T09:00:00.000Z", "2024-04-01T18:00:00.000Z", "hour", "minute"),
  sameTimeLocal: DATE_COMPARE("2023-04-01T09:00:00.000Z", "2024-04-01T18:00:00.000Z", "hour", "minute", "Europe/Berlin", "America/Los_Angeles")
}

[ 
  { 
    "Germany" : "2023-04-01T11:00:00.000", 
    "USA" : "2024-04-01T11:00:00.000", 
    "sameTimeUTC" : false, 
    "sameTimeLocal" : true 
  } 
]

DATE_UTCTOLOCAL()

Introduced in: v3.8.0

Converts date assumed in Zulu time (UTC) to local timezone.

It takes historic daylight saving times into account.

DATE_UTCTOLOCAL(date, timezone, zoneinfo) → date

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string): IANA timezone name , e.g. "America/New_York", "Europe/Berlin" or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). Throws an error if the timezone is not known to ArangoDB.
• zoneinfo (boolean, optional): if set to true, an object with timezone information is returned. The default is false and a date string is returned
• returns date (string|object): an ISO 8601 date time string in unqualified local time, or an object with the following attributes:
  • local (string): ISO 8601 date time string in unqualified local time
  • tzdb (string): version of the timezone database used (e.g. "2020f")
  • zoneInfo: (object): timezone information
    • name (string): timezone abbreviation (GMT, PST, CET, …)
    • begin (string|null): begin of the timezone effect as UTC date time string
    • end (string|null): end of the timezone effect as UTC date time string
    • dst (boolean): true when daylight saving time (DST) is active, false otherwise
    • offset (number): offset to UTC in seconds

Convert a date time string to different local timezones:

RETURN [
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000", "Europe/Berlin"),
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000", "America/New_York"),
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000", "UTC")
]

[ 
  [ 
    "2020-03-15T01:00:00.000", 
    "2020-03-14T20:00:00.000", 
    "2020-03-15T00:00:00.000Z" 
  ] 
]

Convert date time strings with and without UTC indicator (Z), with a timezone offset, and a Unix timestamp to local time:

RETURN [
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000", "Asia/Shanghai"),
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000Z", "Asia/Shanghai"),
  DATE_UTCTOLOCAL("2020-03-15T00:00:00.000-02:00", "Asia/Shanghai"),
  DATE_UTCTOLOCAL(1584230400000, "Asia/Shanghai")
]

[ 
  [ 
    "2020-03-15T08:00:00.000", 
    "2020-03-15T08:00:00.000", 
    "2020-03-15T10:00:00.000", 
    "2020-03-15T08:00:00.000" 
  ] 
]

Convert to local time and include timezone information:

RETURN DATE_UTCTOLOCAL(DATE_NOW(), "Africa/Lagos", true)

[ 
  { 
    "local" : "2024-03-11T20:03:41.782", 
    "tzdb" : "2023a", 
    "zoneInfo" : { 
      "name" : "WAT", 
      "begin" : "1919-08-31T23:30:00.000Z", 
      "end" : null, 
      "dst" : false, 
      "offset" : 3600 
    } 
  } 
]

DATE_LOCALTOUTC()

Introduced in: v3.8.0

Converts date assumed in local timezone to Zulu time (UTC).

It takes historic daylight saving times into account.

DATE_LOCALTOUTC(date, timezone, zoneinfo) → date

• date (number|string): numeric timestamp or ISO 8601 date time string
• timezone (string): IANA timezone name , e.g. "America/New_York", "Europe/Berlin" or "UTC". Use "America/Los_Angeles" for Pacific time (PST/PDT). Throws an error if the timezone is not known to ArangoDB.
• zoneinfo (boolean, optional): if set to true, an object with timezone information is returned. The default is false and a date string is returned
• returns date (string|object): an ISO 8601 date time string in Zulu time (UTC), or an object with the following attributes:
  • utc (string): ISO 8601 date time string in Zulu time (UTC)
  • tzdb (string): version of the timezone database used (e.g. "2020f")
  • zoneInfo: (object): timezone information
    • name (string): timezone abbreviation (GMT, PST, CET, …)
    • begin (string|null): begin of the timezone effect as UTC date time string
    • end (string|null): end of the timezone effect as UTC date time string
    • dst (boolean): true when daylight saving time (DST) is active, false otherwise
    • offset (number): offset to UTC in seconds

Convert a date time string from different local timezones to UTC:

RETURN [
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000", "Europe/Berlin"),
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000", "America/New_York"),
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000", "UTC")
]

[ 
  [ 
    "2020-03-14T23:00:00.000Z", 
    "2020-03-15T04:00:00.000Z", 
    "2020-03-15T00:00:00.000Z" 
  ] 
]

Convert date time strings with and without UTC indicator (Z), with a timezone offset, and a Unix timestamp to UTC time:

RETURN [
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000", "Asia/Shanghai"),
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000Z", "Asia/Shanghai"),
  DATE_LOCALTOUTC("2020-03-15T00:00:00.000-02:00", "Asia/Shanghai"),
  DATE_LOCALTOUTC(1584230400000, "Asia/Shanghai")
]

[ 
  [ 
    "2020-03-14T16:00:00.000Z", 
    "2020-03-14T16:00:00.000Z", 
    "2020-03-14T18:00:00.000Z", 
    "2020-03-14T16:00:00.000Z" 
  ] 
]

Convert to UTC time and include timezone information:

RETURN DATE_LOCALTOUTC("2021-03-16T12:00:00.000", "Africa/Lagos", true)

[ 
  { 
    "utc" : "2021-03-16T11:00:00.000Z", 
    "tzdb" : "2023a", 
    "zoneInfo" : { 
      "name" : "WAT", 
      "begin" : "1919-08-31T23:30:00.000Z", 
      "end" : null, 
      "dst" : false, 
      "offset" : 3600 
    } 
  } 
]

DATE_TIMEZONE()

Introduced in: v3.8.0

Returns system timezone ArangoDB is running on.

For cloud servers, this is most likely "Etc/UTC".

DATE_TIMEZONE() → timezone

• returns timezone (string): IANA timezone name  of the server timezone.

DATE_TIMEZONES()

Introduced in: v3.8.0

Returns all valid timezone names.

DATE_TIMEZONES() → timezones

• returns timezones (array): an array of IANA timezone names 

Working with dates and indexes

There are two recommended ways to store timestamps in ArangoDB:

• string: UTC timestamp with ISO 8601 
• number: unix timestamp  with millisecond precision

The sort order of both is identical due to the sort properties of ISO date strings. You can’t mix both types, numbers and strings, in a single attribute however.

You can use persistent indexes with both date types. When choosing string representations, you can work with string comparisons (less than, greater than etc.) to express time ranges in your queries while still utilizing persistent indexes:

db._create("exampleTime");
var timestamps = [
  "2014-05-07T14:19:09.522",
  "2014-05-07T21:19:09.522",
  "2014-05-08T04:19:09.522",
  "2014-05-08T11:19:09.522",
  "2014-05-08T18:19:09.522"
];
for (i = 0; i < 5; i++) {
  db.exampleTime.save({value:i, ts: timestamps[i]});
}
db._query(`
  FOR d IN exampleTime
    FILTER d.ts > '2014-05-07T14:19:09.522' AND d.ts < '2014-05-08T18:19:09.522'
    RETURN d
`).toArray()

[ArangoCollection 68314, "exampleTime" (type document, status loaded)]
{ 
  "_id" : "exampleTime/68328", 
  "_key" : "68328", 
  "_rev" : "_hg5kLBa--_" 
}

[ 
  { 
    "_key" : "68322", 
    "_id" : "exampleTime/68322", 
    "_rev" : "_hg5kLBW--A", 
    "value" : 1, 
    "ts" : "2014-05-07T21:19:09.522" 
  }, 
  { 
    "_key" : "68324", 
    "_id" : "exampleTime/68324", 
    "_rev" : "_hg5kLBW--B", 
    "value" : 2, 
    "ts" : "2014-05-08T04:19:09.522" 
  }, 
  { 
    "_key" : "68326", 
    "_id" : "exampleTime/68326", 
    "_rev" : "_hg5kLBa---", 
    "value" : 3, 
    "ts" : "2014-05-08T11:19:09.522" 
  } 
]

The first and the last timestamp in the array are excluded from the result by the FILTER.

Limitations

Note that dates before the year 1583 aren’t allowed by the ISO 8601  standard by default, because they lie before the official introduction of the Gregorian calendar and may thus be incorrect or invalid. All AQL date functions apply the same rules to every date according to the Gregorian calendar system, even if inappropriate. That does not constitute a problem, unless you deal with dates prior to 1583 and especially years before Christ. The standard allows negative years, but requires special treatment of positive years too, if negative years are used (e.g. +002015-05-15 and -000753-01-01). This is rarely used however, and AQL does not use the 7-character version for years between 0 and 9999 in ISO strings. Keep in mind that they can’t be properly compared to dates outside that range. Sorting of negative dates does not result in a meaningful order, with years longer ago last, but months, days and the time components in otherwise correct order.

Leap seconds are ignored, just as they are in JavaScript as per ECMAScript Language Specifications .