Mongoose/MongoDB regex find query using variable

Question

I'm trying to perform a find query in mongoDB (using the mongoose framework with nodejs and express) using a regex expression based on a variable. I'm partially able to query correctly using a static string hard-coded into the code but I need to perform the query using a variable who's value is constantly changing.

The query uses three fields (author, date and updated date) and should go through all documents in a scheme and find all documents where 'date' or 'updated date' is like the variable currentDate (always formatted as YYYY-mm-dd) and where author is authorname.

The main issue is that mongoDB stores the dates with ISO format. On execution the string variable (currentDate (YYYY-mm-dd)) is formatted to ISO which leads it to only "hit" documents with time 00:00.000Z as ISO formats 2015-09-17 to be 2015-09-17 00:00:00.000Z (not taking timezone into consideration).

The query:

var currentDate = yyyy-mm-dd;

Scheme.find({$and:[{'local.author': author},
   {$or: [{'local.date': new RegExp(currentDate)}, 
          {'local.updated_date':new RegExp(currentDate)}]}]}

I've also tried with the $gte and $lte variables (where $gte = date today and &lte = date tomorrow (00:00)) but I couldn't get it to work, I met the wall trying to perform the regex.

I hope there is a brilliant regex expert out there who can help, thank you! :)

I got the error: { [CastError: Cast to date failed for value "/\date{4}(-\date{2}){2}/" at path "local.updated_date"] stack: [Getter/Setter], message: 'Cast to date failed for value "/\\date{4}(-\\date{2}){2}/" at path "local.updated_date"', name: 'CastError', kind: 'date', value: /\date{4}(-\date{2}){2}/, path: 'updated.active_date' } — gudbrand3
its not date.. did you try with just /\d{4}(-\d{2}){2}/? — karthik manchala
@karthikmanchala Yes, but I get the following error CastError: Cast to date failed for value "/\d{4}(-\d{2}){2}/" at path "local.updated_date"] I assume I should write it like this? or am I doing it wrong? var currentDate = /\d{4}(-\d{2}){2}/; Scheme.find({$and:[{'local.author': author},{$or: [{'local.date': new RegExp(currentDate)},{'local.updated_date':new RegExp(currentDate)}]}]} Thank you so much for helping! :) — gudbrand3

gudbrand3 gudbrand3 · Accepted Answer · 2015-09-20T09:31:23

I figured it out without all the complexity.

var tomorrow = new Date();
var today = new Date();
tomorrow.setDate(tomorrow.getDate() + 1);
tomorrow.setHours(0,0,0,0);
today.setHours(0,0,0,0);

Scheme.find({$and:[{'local.author': author},{$or: [{'local.date': { $gt: today, $lt: tomorrow } },{'local.updated_date':{ $gte: today, $lt: tomorrow }}]}]}

Mongoose/MongoDB regex find query using variable

1 Answers