ArangoDB v2.8 reached End of Life (EOL) and is no longer supported.

This documentation is outdated. Please see the most recent version here: Try latest

Date functions

AQL offers functionality to work with dates. Dates are no data types of their own in AQL (neither are they in JSON, which is often used as a format to ship data into and out of ArangoDB). Instead, dates in AQL are internally represented by either numbers (timestamps) or strings. The date functions in AQL provide mechanisms to convert from a numeric timestamp to a string representation and vice versa.

There are two date functions in AQL to create dates for further use:

  • DATE_TIMESTAMP(date): Creates a UTC timestamp value from date. The return value has millisecond precision. To convert the return value to seconds, divide it by 1000.

  • DATE_TIMESTAMP(year, month, day, hour, minute, second, millisecond): Same as before, but allows specifying the individual date components separately. All parameters after day are optional.

  • DATE_ISO8601(date): Returns an ISO8601 date time string from date. The date time string will always use UTC time, indicated by the Z at its end.

  • DATE_ISO8601(year, month, day, hour, minute, second, millisecond): same as before, but allows specifying the individual date components separately. All parameters after day are optional.

These two above date functions accept the following input values:

  • numeric timestamps, indicating the number of milliseconds elapsed since the UNIX epoch (i.e. January 1st 1970 00:00:00 UTC). An example timestamp value is 1399472349522, which translates to 2014-05-07T14:19:09.522Z.

  • date time strings in formats YYYY-MM-DDTHH:MM:SS.MMM, YYYY-MM-DD HH:MM:SS.MMM, or YYYY-MM-DD Milliseconds are always optional. A timezone difference 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.

    Please note that if no timezone offset is specified in a date string, ArangoDB will assume UTC time automatically. This is done to ensure portability of queries across servers with different timezone settings, and because timestamps will always be UTC-based.

  • individual date components as separate function arguments, in the following order:

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

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

The following calls to DATE_TIMESTAMP are equivalent and will 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 will return “2014-05-07T14:19:09.522Z”.

The following date functions can be used with dates created by DATE_TIMESTAMP and DATE_ISO8601:

  • DATE_DAYOFWEEK(date): Returns the weekday number of date. The return values have the following meanings:
    • 0: Sunday
    • 1: Monday
    • 2: Tuesday
    • 3: Wednesday
    • 4: Thursday
    • 5: Friday
    • 6: Saturday

  • DATE_YEAR(date): Returns the year part of date as a number.

  • DATE_MONTH(date): Returns the month part of date as a number.

  • DATE_DAY(date): Returns the day part of date as a number.

  • DATE_HOUR(date): Returns the hour part of date as a number.

  • DATE_MINUTE(date): Returns the minute part of date as a number.

  • DATE_SECOND(date): Returns the seconds part of date as a number.

  • DATE_MILLISECOND(date): Returns the milliseconds part of date as a number.

  • DATE_DAYOFYEAR(date): Returns the day of year number of date. The return values range from 1 to 365, or 366 in a leap year respectively.

  • DATE_ISOWEEK(date): Returns the ISO week date 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.

  • DATE_LEAPYEAR(date): Returns whether the year of date is a leap year.

  • DATE_QUARTER(date): Returns the quarter of the given date (1-based):

    • 1: January, February, March
    • 2: April, May, June
    • 3: July, August, September
    • 4: October, November, December
  • DATE_DAYS_IN_MONTH(date): Returns the number of days in date’s month (28..31).

The following other date functions are also available:

  • DATE_NOW(): Returns the current 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.

  • DATE_ADD(date, amount, unit): Adds amount given in unit to date and returns the calculated date.

    unit can be 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

    amount is the 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.

    Examples:

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!)

You may also pass an ISO duration string as amount and leave out unit. The format is P_Y_M_W_DT_H_M_._S, where underscores stand for digits and letters for time intervals - except for 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 amounts and time intervals.

Examples:

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, amount, unit): Subtracts amount given in unit from date and returns 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).

    Examples:

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(date1, date2, unit, asFloat): Calculate the difference between two dates in given time unit, optionally with decimal places. Returns a negative value if date1 is greater than / after date2.

    unit can be 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

  • DATE_COMPARE(date1, date2, unitRangeStart, unitRangeEnd): Compare two partial dates and return true if they 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. Pass the unit to start from as unitRangeStart, and the unit to end with as unitRangeEnd. All units in between will be compared. Leave out unitRangeEnd to only compare unitRangeStart. 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

    An error is raised if unitRangeEnd is a unit before unitRangeStart.

    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")

// Will only match 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 !=) will 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 for instance:

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 2015-05-15 in a string comparison (e.g. 2015-05-15T11:30:00.000Z). The time components will be “ignored”. The equal sign in >= merely helps to express the semantic. Dates before 2015-05-15 are less and therefore filtered out. The second condition works likewise. The query will return 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.

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
  • DATE_FORMAT(date, format): Format a date according to the given format string. It 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 date (1..53)
    • %kk: ISO week date (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 will used instead. %yy preserves the sign for negative years and thus returns 3 characters in total.

    Single % characters will be 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 this 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
  • IS_DATESTRING(value): Returns 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 will return false for all non-string values, even if some of them may be usable in date functions.

Working with dates and indices

There are two recommended ways to store timestamps in ArangoDB:

The sort order of both is identical due to the sort properties of ISO date strings. Therefore, you can work with skiplist indices and use string comparisons (less than, greater than, in, equality) to express time ranges in your queries:

arangosh> db._create("exampleTime");
arangosh> 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"];
arangosh> for (i = 0; i < 5; i++) db.exampleTime.save({value:i, ts: timestamps[i]})
arangosh> 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()
Show execution results
[ArangoCollection 2428782721, "exampleTime" (type document, status loaded)]
[ 
  { 
    "value" : 3, 
    "ts" : "2014-05-08T11:19:09.522", 
    "_id" : "exampleTime/2429765761", 
    "_rev" : "2429765761", 
    "_key" : "2429765761" 
  }, 
  { 
    "value" : 1, 
    "ts" : "2014-05-07T21:19:09.522", 
    "_id" : "exampleTime/2429372545", 
    "_rev" : "2429372545", 
    "_key" : "2429372545" 
  }, 
  { 
    "value" : 2, 
    "ts" : "2014-05-08T04:19:09.522", 
    "_id" : "exampleTime/2429569153", 
    "_rev" : "2429569153", 
    "_key" : "2429569153" 
  } 
]
Hide execution results

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. Leap seconds are ignored, just as they are in JavaScript as per ECMAScript Language Specifications.

❮  Numeric

© ArangoDB - the native multi-model NoSQL database