0

I need to return a value for every date between a start and end date.

For each row: if the date exists in row.date, then I need to return row.myValue else I need to return null.

Example:

dateRange = {start: '2018-01-01', end: '2018-01-03'}
aqlCollection = [
  {date: '2018-01-01', myVal: 1},
  {date: '2018-01-02', myVal: 2},
  {date: '2017-05-18', myVal: 3}
]

This should return:

[
  {'2018-01-01': 1},
  {'2018-01-02': 2},
  {'2018-01-03': null}
]

This can be accomplished simply if there was a WHILE loop in arangodb. Or if I could for loop using an incrementer instead of doing FOR date IN dates then I could just say FOR date=startRange; date<endRange; date+=24hrs.

Any ideas on how this can be achieved within an Arango query? If it is not possible I will do an O(n) loop over after a simple group by date query and add in all the dates that do not exist.

James
  • 445
  • 1
  • 6
  • 19

1 Answers1

0

Please take a look at: https://docs.arangodb.com/3.4/AQL/Functions/Date.html.

If you have an attribute in the date format then you can enumerate the collection's items using a for-loop and use a filter on the attribute to get the dates in a certain range. As you already showed. Adding an index on the attribute will give you a nice speed up. It will make sure that only objects with matching dates will be inspected.

Jan Christoph Uhde
  • 701
  • 5
  • 10
  • Yes but I need all dates within a certain range, not just the ones that exist in the collection. Doing a filter for dates will return only dates that exist in the data. – James Jan 24 '19 at 10:32
  • 1
    Sorry I was just called, when I started reading your question and did not read carefully enough. While I guess it would be better still to handle that in the client or in a different way you can try something like this: "FOR d IN 0..20 RETURN DATE_FORMAT(DATE_NOW()+d*3600*24*1000,'%yyyy-%mm-%dd')" – Jan Christoph Uhde Jan 24 '19 at 11:30
  • yeah probably best done outside the query, thank you for the help! – James Jan 24 '19 at 12:07