Find entry whose date is within a minute from now, but regardless of the day

user_14018972 0
Viewed 77

I have made a query that finds all entries where the date field is within the next minute:

import { addMinutes, format } from 'date-fns'
date: {
    $gte: new Date(),
    $lt: (addMinutes(new Date(), 1))
}

However, this only works if the date is also on the same day as today. Now, if I want this to work regardless of which day is in the date field, what should I do?

The usecase is the following: The date field is set to Date.now() when the document is created. I want to get all documents that would be created within a minute from now, if there was no year, month nor day. This should also wrap around hours and days, so if it's currently 23:59:30, I want do find all documents that were created on any day at 23:59:30 or later, OR before 00:00:30.

basically I would want to extract only the hour minute and second from my date field, and work with those. But I can't find a way to do it.

Here is an example document:

    "_id": "5f2447d259aaf231d8dc1d3b",
    "date": "2020-08-07T12:22:00.000Z",
    "enabled": true,
mongodb
1 Answers

This works and accounts for overlap at midnight. It requires some vars from js so I can't do a playground. It works by converting any given time to its minute in the day. 00:00:30 = 0.5 and 23:59:30 = 1439.5. Then the $match can use a combination of $gt $lt operators depending on the current time.

Depending on the size of your collection you may want to add the minute value to your schema and then compute it when saving docs. Then the $addFields stage can be removed and you can use indexing. Right now this scans all docs.

Edit: I just re-read you question, and see your only looking for after the current time. Edited to only include docs within the next minute

// Set the range 'fromNow' to query docs
const range = 1; // minutes

const now = new Date(); // Insert a date to test overlaps '2020-03-12T23:59:50.000+00:00'

// Get the current minute of the day in a usualable format ie: 1200.42
const currentMinute = 
  now.getUTCHours()*60 + now.getUTCMinutes() + now.getUTCSeconds()/60;

const result = await Item.aggregate([
  // Convert the date of each doc to the same 'currentMinute' format ie: 1200.42
  {$addFields: {
    time: {$add:[
      {$multiply: [{$hour: '$date'}, 60]},
      {$minute: '$date'},
      {$divide: [{$second: '$date'}, 60]}
    ]},
  }},

  {$match: {
    $expr: {$or: [
      // Match any docs that time are within the range
      {$and: [
        // The docs date is after now
        {$gte: ['$time', currentMinute]},
        {$lte: ['$time', currentMinute + range]}
      ]},

      // Now account for days end
      // If its 11:59:31 then include docs before 00:00:31
      {$cond: [
        // If current minute + range is greater than day end (1440)
        {$gt: [currentMinute + range, 1440]},
        // Then include docs that's time is less than currentMin + range - 1440
        {$lte: ['$time', currentMinute + range - 1440]},
        // Else null to ignore this $match stage
        null
      ]},
    ]}
  }}
]);

console.log('result', result);
user_138856150
Related