How create an array of dates?

Working with Data
, ,
1

Hi to everybody,
for my query i need to create an calendar array for store all days between two input dates. For example:date1:ISODate("2018-02-12) and date2:ISODate("2018-03-12), in output i want a document like this:

days:[
{day:"2018-02-12T00:00:00.000Z"},
{day:"2018-02-13T00:00:00.000Z"},
{day:"2018-02-14T00:00:00.000Z"},
{day:"2018-02-15T00:00:00.000Z"},
{day:"2018-02-16T00:00:00.000Z"},
{day:"2018-02-17T00:00:00.000Z"},
{day:"2018-02-18T00:00:00.000Z"},
{day:"2018-02-19T00:00:00.000Z"},
{day:"2018-02-20T00:00:00.000Z"},
{day:"2018-02-21T00:00:00.000Z"},
{day:"2018-02-22T00:00:00.000Z"},
{day:"2018-02-23T00:00:00.000Z"},
{day:"2018-02-24T00:00:00.000Z"},
{day:"2018-02-25T00:00:00.000Z"},
{day:"2018-02-26T00:00:00.000Z"},
{day:"2018-02-27T00:00:00.000Z"},
{day:"2018-02-28T00:00:00.000Z"},
{day:"2018-03-01T00:00:00.000Z"},
{day:"2018-03-02T00:00:00.000Z"},
{day:"2018-03-03T00:00:00.000Z"},
{day:"2018-03-04T00:00:00.000Z"},
{day:"2018-03-05T00:00:00.000Z"},
{day:"2018-03-06T00:00:00.000Z"},
{day:"2018-03-07T00:00:00.000Z"},
{day:"2018-03-08T00:00:00.000Z"},
{day:"2018-03-09T00:00:00.000Z"},
{day:"2018-03-10T00:00:00.000Z"},
{day:"2018-03-11T00:00:00.000Z"},
{day:"2018-03-12T00:00:00.000Z"},

I have a solution if the two dates are in the same month but if i have different months o years my code doesn’t work. My code is the following:

{set:{
   dates:{
     $map:{
       input:
       {
          $range:
          [0, 
          {$subtract:[{$add:[1,"$endDate"]}, "$startDate"]},
          1000*60*60*24]
         
       },
      in:{$add:["$startDate","$$this"]}
       
     }}
}
}

I want this because at the end i compare this calendar with another calendar2 and check if all days between the two input dates are presents in calendar2.
Thank you :slight_smile:

2

If i have this dates:

startDate: new ISODate('2014-06-01'),
endDate:new ISODate('2014-07-22')

I have the follwing erros:

PlanExecutor error during aggregation :: caused by :: $range requires an ending value that can be represented as a 32-bit integer, found value: 4406400001
3

It seems that is related to the number of days between the 2 dates rather than the month/year crossing.

Here is a slightly modified algorithm based on the same principals as the one posted. The difference is that rather than having a range based on the millisecs between the 2 dates, it creates a range of the number of days between the 2 dates.

startDate: new ISODate("2014-06-01") 
endDate:new ISODate("2014-07-22")
one_day = 24 * 60 * 60 * 1000
set_start_end = { "$set" : { startDate , endDate } }
// for readability and to ease of debugging, the range will be created in its own stage
set_range = { "$set" : { range: {  '$range':
  [ 0 ,
    {  '$divide': [ { '$subtract': [ '$endDate', '$startDate' ] }, one_day ]   }
  ]
}
// the stage that uses the range to produce the dates
set_dates = { '$set': { "dates" : {  '$map': {
        input: '$range',
        in: {
          '$add': [ '$start_date', { '$multiply' : [ '$$this', one_day ] } ]
        }
} } } }

pipeline = [ set_start_end , set_range , set_dates ]

However, I stand by comment in the original post:

What you are trying to do is probably easier to do in your application code rather that try to do it on the server. Also doing this on the server will increase the data transferred from the server to the application.

Extra work done by the server is harder to scale compared to do it at the application level. For example, 2 users asks for year worth of data forces the server to generate the 2 ranges. If you just asked the data that is present, rather than generating 0 counts, then 2 ranges are generated by 2 different instances of the application. Auto-scaled.

And by reading

I really wonder why you want the database server to generated dates with 0 count if you are going to compare the 2 calandar anyway?

1 Like
4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.