How can I use mongodump to dump out records matching a specific date range?


Mongodb Problem Overview

I'm trying to use the mongodump command to dump out a bunch of records created on a specific date. The records include a "ts" field which is a MongoDB Date() object.

mongodump takes a -q argument which can be used to run a query to select the records to be included in the dump. Unfortunately, the -q argument needs to be provided in JSON, and it's not clear how to express a "less-than-this-date, more-than-this-date" query in pure JSON (normally such queries would use a 'new Date()' constructor)"

Any tips? I've tried using the {$date: unix-timestamp-in-milliseconds} format but it's not working for me.

Mongodb Solutions

Solution 1 - Mongodb

I solved it - the magic incantation I was looking for is:

mongodump --query "{\"ts\":{\"\$gt\":{\"\$date\":`date -d 2011-08-10 +%s`000},\"\$lte\":{\"\$date\":`date -d 2011-08-11 +%s`000}}}"

Solution 2 - Mongodb

A more human-readable version than @SimonWillison's escaped version:

--query "{ time: { \$gt: new Date(1312959600000), \$lt: new Date(1313046000000) }}"

(Note the dollarsigns still need to be escaped.)

I got the millisecond timestamps by creating dates in the shell, e.g.:

> var targetDateStart = new Date(2011, 7, 10);
> var targetDateEnd = new Date(2011, 7, 11);
> targetDateStart.getTime();
> targetDateEnd.getTime();

Solution 3 - Mongodb

In MongoDB 3.2, we can use --queryFile option with mongodump.

first of all, create a json file:

{"serverTime": {"$gte": ISODate("2016-01-30T16:00:00.000Z"), "$lt": ISODate("2016-01-31T16:00:00.000Z")}}

next,use mongodump:

mongodump --db <dbName> --collection <collectionName> --queryFile query.json

simple and clear.

Solution 4 - Mongodb

Edit: fixed typos

Add an update:

  1. mongodump --query doesn't support IsoDate, but accepts Date in milliseconds form.

  2. As date command behaves different in OS X, date -d 2011-08-10 +%s does not work for me. If you've run into the same issue, try to read the manual or use this:

  • Get current time in seconds:

        date -j -f "%a %b %d %T %Z %Y" "`date`" "+%s"
  • Get specific time in seconds:

        date -j -f "%Y-%m-%d %H:%M:%S" "2014-01-01 00:00:00"  "+%s"
  1. Use the single quote version to avoid escaping.

     mongodump --query '{updated_at: { $gte: Date(1403280000000) } }'

Solution 5 - Mongodb

This should work, what didn't work about your $date query? :

mongodump --query  {"ts":{$gt:{$date:178929000}}}

Solution 6 - Mongodb

use single quotes around the query. I found that ISODate() doesn't work.

mongodump --query  '{"ts":{$gt:{$date:178929000}}}'

Solution 7 - Mongodb

Extended JSON Format works, as can be found in the documentation ( Example:

--query '{ "timest": { "$gte": { "$date": "2020-08-19T00:00:00.000Z" } } }'

Solution 8 - Mongodb

In my case I queried entries created 14 days ago and end up with this bash script:

date_now=`date +%s%3N`
date_2weeks_ago=$[date_now - 14 * 24 * 60 * 60 * 1000]
query=$(printf '{ createdAt: { $gte: Date(%d) } }' $date_2weeks_ago)
echo $query > query.json
mongodump \
--collection=data \
rm query.json

mongodump version: r4.0.12


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSimon WillisonView Question on Stackoverflow
Solution 1 - MongodbSimon WillisonView Answer on Stackoverflow
Solution 2 - MongodbericsocoView Answer on Stackoverflow
Solution 3 - MongodbdiggzhangView Answer on Stackoverflow
Solution 4 - MongodbifyouseewendyView Answer on Stackoverflow
Solution 5 - MongodbRemon van VlietView Answer on Stackoverflow
Solution 6 - MongodbmcrView Answer on Stackoverflow
Solution 7 - MongodbrStormsView Answer on Stackoverflow
Solution 8 - MongodbDmitriy BotovView Answer on Stackoverflow