Additional AQL Date functions (experimental)

aql-date.diff

diff --git a/arangod/Aql/Executor.cpp b/arangod/Aql/Executor.cpp
index b3017b6..3eb0d11 100644
--- a/arangod/Aql/Executor.cpp
+++ b/arangod/Aql/Executor.cpp
@@ -238,6 +238,9 @@ std::unordered_map<std::string, Function const> const Executor::FunctionNames{
   { "DATE_TIMESTAMP",              Function("DATE_TIMESTAMP",              "AQL_DATE_TIMESTAMP", "ns|ns,ns,ns,ns,ns,ns", true, true, false, true, true) },
   { "DATE_ISO8601",                Function("DATE_ISO8601",                "AQL_DATE_ISO8601", "ns|ns,ns,ns,ns,ns,ns", true, true, false, true, true) },
   { "DATE_DAYOFWEEK",              Function("DATE_DAYOFWEEK",              "AQL_DATE_DAYOFWEEK", "ns", true, true, false, true, true) },
+  { "DATE_DAYOFYEAR",              Function("DATE_DAYOFYEAR",              "AQL_DATE_DAYOFYEAR", "ns", true, true, false, true, true) },
+  { "DATE_LEAPYEAR",               Function("DATE_LEAPYEAR",               "AQL_DATE_LEAPYEAR", "ns", true, true, false, true, true) },
+  { "DATE_ISOWEEK",                Function("DATE_ISOWEEK",                "AQL_DATE_ISOWEEK", "ns", true, true, false, true, true) },
   { "DATE_YEAR",                   Function("DATE_YEAR",                   "AQL_DATE_YEAR", "ns", true, true, false, true, true) },
   { "DATE_MONTH",                  Function("DATE_MONTH",                  "AQL_DATE_MONTH", "ns", true, true, false, true, true) },
   { "DATE_DAY",                    Function("DATE_DAY",                    "AQL_DATE_DAY", "ns", true, true, false, true, true) },
@@ -245,6 +248,7 @@ std::unordered_map<std::string, Function const> const Executor::FunctionNames{
   { "DATE_MINUTE",                 Function("DATE_MINUTE",                 "AQL_DATE_MINUTE", "ns", true, true, false, true, true) },
   { "DATE_SECOND",                 Function("DATE_SECOND",                 "AQL_DATE_SECOND", "ns", true, true, false, true, true) },
   { "DATE_MILLISECOND",            Function("DATE_MILLISECOND",            "AQL_DATE_MILLISECOND", "ns", true, true, false, true, true) },
+  { "DATE_CALC",                   Function("DATE_CALC",                   "AQL_DATE_CALC", "ns,s,n", true, true, false, true, true) },
 
   // misc functions
   { "FAIL",                        Function("FAIL",                        "AQL_FAIL", "|s", false, false, true, true, true) },
diff --git a/js/server/modules/org/arangodb/aql.js b/js/server/modules/org/arangodb/aql.js
index 9914ad5..02e95cb 100644
--- a/js/server/modules/org/arangodb/aql.js
+++ b/js/server/modules/org/arangodb/aql.js
@@ -4494,6 +4494,25 @@ function AQL_DATE_DAYOFWEEK (value) {
 }
 
 ////////////////////////////////////////////////////////////////////////////////
+/// @brief return the ISO week date of the date passed (1..53)
+////////////////////////////////////////////////////////////////////////////////
+
+function AQL_DATE_ISOWEEK (value) {
+  'use strict';
+  
+  try {
+    var date = MAKE_DATE([ value ], "DATE_ISOWEEK");
+    date.setUTCHours(0, 0, 0, 0);
+    date.setUTCDate(date.getUTCDate() + 4 - (date.getUTCDay() || 7));
+    return Math.ceil((((date - Date.UTC(date.getUTCFullYear(), 0, 1)) / 864e5) + 1) / 7);
+  }
+  catch (err) {
+    WARN("DATE_ISOWEEK", INTERNAL.errors.ERROR_QUERY_INVALID_DATE_VALUE);
+    return null;
+  }
+}
+
+////////////////////////////////////////////////////////////////////////////////
 /// @brief return the year of the date passed
 ////////////////////////////////////////////////////////////////////////////////
 
@@ -4605,6 +4624,150 @@ function AQL_DATE_MILLISECOND (value) {
   }
 }
 
+////////////////////////////////////////////////////////////////////////////////
+/// @brief add/subtract a time unit and return the calculated date of the date passed
+////////////////////////////////////////////////////////////////////////////////
+
+function AQL_DATE_CALC (value, unit, amount) {
+  'use strict';
+  
+  try {
+    var date = MAKE_DATE([ value ], "DATE_CALC");
+    var unitGetter;
+    var unitSetter;
+    switch (unit.toLowerCase()){
+      case "y":
+      case "year":
+      case "years":
+        unitGetter = "getUTCFullYear";
+        unitSetter = "setUTCFullYear";
+        break;
+      case "m":
+        if (unit == "M") {
+            unitGetter = "getUTCMonth";
+            unitSetter = "setUTCMonth";
+        } else {
+            unitGetter = "getUTCMinutes";
+            unitSetter = "setUTCMinutes";
+        }
+        break;
+      case "month":
+      case "months":
+        unitGetter = "getUTCMonth";
+        unitSetter = "setUTCMonth";
+        break;
+      case "d":
+      case "day":
+      case "days":
+        unitGetter = "getUTCDate";
+        unitSetter = "setUTCDate";
+        break;
+      case "h":
+      case "hour":
+      case "hours":
+        unitGetter = "getUTCHours";
+        unitSetter = "setUTCHours";
+        break;
+      // "m" already handled above
+      case "minute":
+      case "minutes":
+        unitGetter = "getUTCMinutes";
+        unitSetter = "setUTCMinutes";
+        break;
+      case "s":
+      case "second":
+      case "seconds":
+        unitGetter = "getUTCSeconds";
+        unitSetter = "setUTCSeconds";
+        break;
+      case "ms":
+      case "millisecond":
+      case "milliseconds":
+        unitGetter = "getUTCMilliseconds";
+        unitSetter = "setUTCMilliseconds";
+        break;
+      default:
+        // TODO: distinct error?
+        //WARN("DATE_CALC", INTERNAL.errors.ERROR_QUERY_INVALID_DATE_VALUE);
+        return null;
+    }
+    date[unitSetter](date[unitGetter]() + amount);
+    return date;
+  }
+  catch (err) {
+    WARN("DATE_CALC", INTERNAL.errors.ERROR_QUERY_INVALID_DATE_VALUE);
+    return null;
+  }
+}
+
+////////////////////////////////////////////////////////////////////////////////
+/// @brief return if year of the date passed is a leap year
+////////////////////////////////////////////////////////////////////////////////
+
+function AQL_DATE_LEAPYEAR(value) {
+  'use strict';
+
+  try {
+    var yr = MAKE_DATE([ value ], "DATE_LEAPYEAR").getUTCFullYear();
+    return !((yr % 4) || (!(yr % 100) && (yr % 400)));
+  }
+  catch (err) {
+    WARN("DATE_LEAPYEAR", INTERNAL.errors.ERROR_QUERY_INVALID_DATE_VALUE);
+    return null;
+  }
+}
+
+////////////////////////////////////////////////////////////////////////////////
+/// @brief return the day of the year of the date passed
+////////////////////////////////////////////////////////////////////////////////
+
+function AQL_DATE_DAYOFYEAR(value) {
+  'use strict';
+
+  try {
+    var dayOfYearOffsets = [
+      0,
+      31, // + 31 Jan
+      59, // + 28 Feb *
+      90, // + 31 Mar
+      120, // + 30 Apr
+      151, // + 31 May
+      181, // + 30 Jun
+      212, // + 31 Jul
+      243, // + 31 Aug
+      273, // + 30 Sep
+      304, // + 31 Oct
+      334 // + 30 Nov
+    ];
+        
+    var dayOfLeapYearOffsets = [
+      0,
+      31, // + 31 Jan
+      59, // + 29 Feb *
+      91, // + 31 Mar
+      121, // + 30 Apr
+      152, // + 31 May
+      182, // + 30 Jun
+      213, // + 31 Jul
+      244, // + 31 Aug
+      274, // + 30 Sep
+      305, // + 31 Oct
+      335 // + 30 Nov
+    ];
+    var date = MAKE_DATE([ value ], "DATE_DAYOFYEAR");
+    var m = date.getUTCMonth();
+    var d = date.getUTCDate();
+    //var ly = AQL_DATE_LEAPYEAR(date);
+    var yr = date.getUTCFullYear();
+    var ly = !((yr % 4) || (!(yr % 100) && (yr % 400)));
+    return (ly ? (dayOfLeapYearOffsets[m] + d) : (dayOfYearOffsets[m] + d));
+  }
+  catch (err) {
+    WARN("DATE_DAYOFYEAR", INTERNAL.errors.ERROR_QUERY_INVALID_DATE_VALUE);
+    return null;
+  }
+}   
+
 // -----------------------------------------------------------------------------
 // --SECTION--                                                   graph functions
 // -----------------------------------------------------------------------------
@@ -8546,6 +8709,9 @@ exports.AQL_DATE_NOW = AQL_DATE_NOW;
 exports.AQL_DATE_TIMESTAMP = AQL_DATE_TIMESTAMP;
 exports.AQL_DATE_ISO8601 = AQL_DATE_ISO8601;
 exports.AQL_DATE_DAYOFWEEK = AQL_DATE_DAYOFWEEK;
+exports.AQL_DATE_DAYOFYEAR = AQL_DATE_DAYOFYEAR;
+exports.AQL_DATE_LEAPYEAR = AQL_DATE_LEAPYEAR;
+exports.AQL_DATE_ISOWEEK = AQL_DATE_ISOWEEK;
 exports.AQL_DATE_YEAR = AQL_DATE_YEAR;
 exports.AQL_DATE_MONTH = AQL_DATE_MONTH;
 exports.AQL_DATE_DAY = AQL_DATE_DAY;
@@ -8553,6 +8719,7 @@ exports.AQL_DATE_HOUR = AQL_DATE_HOUR;
 exports.AQL_DATE_MINUTE = AQL_DATE_MINUTE;
 exports.AQL_DATE_SECOND = AQL_DATE_SECOND;
 exports.AQL_DATE_MILLISECOND = AQL_DATE_MILLISECOND;
+exports.AQL_DATE_CALC = AQL_DATE_CALC;
 
 exports.reload = reloadUserFunctions;
 

Days in month in AQL:

LET dates = [
    "2014-02-15",
    "2016-02-15",
    "2016-03-01",
    "2017-09-30"
]
LET daysInMonth = [29, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]

FOR d IN dates
    RETURN [d, daysInMonth[DATE_MONTH(d) == 2 && DATE_LEAPYEAR(TO_STRING(DATE_YEAR(d))) ? 0 : DATE_MONTH(d)]]

Or with currently available AQL functions:

FOR d IN dates
    LET yr = DATE_YEAR(d)
    RETURN [d, daysInMonth[DATE_MONTH(d) == 2 && (!((yr % 4) || (!(yr % 100) && (yr % 400)))) ? 0 : DATE_MONTH(d)]]

DATE_QUARTER() is buggy, but I fixed it - forgot to adapt it for JS, which is 0-based for months.

Added an isNaN() check to MAKE_DATE(), because it didn't warn if new Date() failed to create a proper date. Had to add another check in DATE_DIFF() still, to not return a number in case one or both dates are invalid (I made MAKE_DATE() return null and check if either or both of the dates given to DATE_DIFF() are null after the call and return null itself if that's the case). A JS throw to execute the catch-block, which warns and returns null, did not work - gave me "internal error while optimizing AST". I guess I would need to set canThrow in Executor.cpp to true for that, but it would actually issue a redundant warning. So just returning null in DATE_DIFF() and let MAKE_DATE() warn about it:

CoDEmanX/ArangoDB@77695b7

Great job again 👍

Don't forget to remove "maybe this isn't what we want to do?" from my comment before submitting the PR. The alternative would be a separate method for years, but with the ambiguity of "year" as a unit, I think this is a better implementation, and moment.js does the same. It just needs to be documented.

Regarding DATE_IS_BIRTHDAY()

I'm in general much in favor of "syntactic sugar" if the use case is strong enough (like classes, fat arrows, destructuring assignment, or str.includes() in ES2015). However if it isn't, I think it's suboptimal and could even increase the threshold for learning a language. DATE_IS_BIRTHDAY() is very readable and easy to understand, for checking someone's birthday, but I think when you feel it's justified to design such specific features you might be better off looking for a terse but more versatile alternative, which once you've learned can be used to solve other problems. If a function is very specific/limited, people might not even look for it in the manual or try "googling" using terms similar enough, or if they have read about it they might not remember once they actually need it. Our brains create neural pathways when we solve problems, which is how we learn and create memories. We then generally prefer to apply the same patterns to different situations to save time and energy, and we tend to overall ignore excessive information ("cognitive miser").

People might also use it for hacks like DATE_IS_BIRTHDAY(DATE_ADD(e.date, 7, "d")) to check if the anniversary of an event is in a week, because they'd find it easier than using DATE_MONTH(DATE_ADD(e.date, 7, "d")) == DATE_MONTH(DATE_NOW()) && DATE_DAY(DATE_ADD(e.date, 7, "d")) == DATE_DAY(DATE_NOW()). For instance in CSS text-indent seems to be mostly used to hide something for eyes, but not screen readers, or replacing images for high pixel density screens. I don't think I've used it to actually indent text in years.

I don't think it's a big deal with DATE_IS_BIRTHDAY() either way, but I just wanted to add a little philosophy to the context. There are no generic rules to follow. Some people like languages like Go because it's "small" and consistent while others like Scala, for the opposite reason.

Maybe DATE_MATCH(date1, date2, units) and/or DATE_PART(date, units), where units would be an array of units? DATE_PART() is pretty standard in sql (but not with array units) and it would be better if you want to check "was user born in february 13?" (or you could use DATE_MONTH()and DATE_DAY()).

I'm not sure I want DATE_END() either. It wouldn't be very useful for presentation. I was thinking it would be useful in queries like "Find all events between the date of event x and the end of the month/year/day of the same event", but that's probably not a very common query either.

Units

I think time units as well as other units are typically expressed as singular outside a given context, and hence when thought of as "arguments" it makes sense with singular, but I agree days just looks better than day, so I also have a slight preference for plural. MS SQL uses singular and abbreviations: https://msdn.microsoft.com/en-us/library/ms189794.aspx, while moment.js as you know uses plural (only?).

I understand why you don't want the same letter for month and minute. Have you considered "i" for minute? That would make DATE_DIFF()s unit arguments compatible with php's date_format, and MySql also uses "%i" for minutes.

DATE_ADD args

DATE_ADD(date, amount, unit) feels more intuitive to me, and it's more similar to mysql.

Regarding the drawback, I'm not a C++ or Arango dev, so I can't be of much help. Not sure if you're talking about an extra line of code in Executor.cpp, better handling of memory, or if it's plain impossible with ISO duration.

Oh, and I'm not getting any notifications here and have to manually check for updates. Maybe you could enable issue tracking on your fork as previously suggested?

Yeah, me neither... Let's continue here: CoDEmanX/ArangoDB#1