68

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.

Simon Willison
  • 15,642
  • 5
  • 36
  • 44

9 Answers9

77

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}}}"
Simon Willison
  • 15,642
  • 5
  • 36
  • 44
  • 2
    don't be afraid to accept this as the answer, worked for me - didn't realize I had to escape the dollar signs. – kmfk Nov 16 '12 at 20:11
  • is there documentation somewhere for the date-in-JSON format you're using here? what is `\`date -d 2011-08-10 +%s\`000` all about? – ericsoco Jan 15 '13 at 00:03
  • @ericsoco looks like he's running a shell command to generate the date – colllin Jan 15 '13 at 06:11
  • Worked for me too. It may not be as human readable as other solutions, but the date part it's the most human readable part compare to 13-digit number that you should make it every time! Thanks Simon. – Maziyar Nov 24 '13 at 04:53
  • He is running the shell command to convert the date to a unix timestamp and then appending `000` to make it milliseconds. – Shiplu Mokaddim Mar 12 '15 at 10:36
  • 5
    You can escape the json by wrapping it in single quotes. – Zlatko Oct 26 '15 at 11:39
32

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();
1312959600000
> targetDateEnd.getTime();
1313046000000
ericsoco
  • 24,913
  • 29
  • 97
  • 127
  • 15
    `$` is not needed to be escaped if you use single quote `'`. In fact this is recommended way, `'{ time: { $gt: new Date(1312959600000), $lt: new Date(1313046000000) }}'` – Shiplu Mokaddim Mar 12 '15 at 10:38
29

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

first of all, create a json file:

//query.json
{"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.

diggzhang
  • 517
  • 1
  • 6
  • 11
  • I've the same issue, need to use new Date instead of ISODate //query.json {"serverTime": {"$gte": new Date("2016-01-30T16:00:00.000Z"), "$lt": new Date("2016-01-31T16:00:00.000Z")}} – Vasanth Umapathy Sep 18 '19 at 18:01
11

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"

  3. Use the single quote version to avoid escaping.

    mongodump --query '{updated_at: { $gte: Date(1403280000000) } }'
Jeffrey C
  • 364
  • 4
  • 13
ifyouseewendy
  • 6,674
  • 1
  • 21
  • 26
  • First command `Failed conversion of \`\`Mar 15 sep 2015 10:49:19 CEST'' using format \`\`%a %b %d %T %Z %Y''` Second command gives me the same as javascripts `Date.UTC(...) / 1000` but differs from `ISODate(...).getTime() / 1000` (by 2 hours because I'm in France) – Rivenfall Sep 15 '15 at 09:12
  • It lacks a bracket. The correct syntax is: `mongodump --query '{updated_at: { $gte: Date(1403280000000) } }'` – midudev Oct 07 '15 at 09:59
5

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

mongodump --query  '{"ts":{$gt:{$date:178929000}}}'
mcr
  • 4,615
  • 2
  • 31
  • 30
5

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

mongodump --query  {"ts":{$gt:{$date:178929000}}}
Remon van Vliet
  • 18,365
  • 3
  • 52
  • 57
  • Mongo shell won't recognize the `$date` operator , so you need to use `Date()` or `ISODate()` https://stackoverflow.com/a/26227744/404699 – steampowered Apr 10 '19 at 20:35
4

Extended JSON Format works, as can be found in the documentation (https://docs.mongodb.com/database-tools/mongodump/). Example:

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

rStorms
  • 1,036
  • 2
  • 11
  • 23
0

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

#!/bin/bash
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 \
--queryFile=query.json
rm query.json

mongodump version: r4.0.12

Dmitriy Botov
  • 2,623
  • 1
  • 15
  • 12
0

In mongodump version 100, I couldn't get the previous solutions to work.

This is one solution that worked for me:

  • Get the unix epoch for the datetime that you want to get the records after. The simplest way could be to run new Date(<your date>).getTime() in your browser console or a Javascript REPL.

Example: new Date('2022-07-01').getTime() -> 1656633600000

  • Create a json queryfile, eg. queryFile.json:
{
  "updatedAt": {
    "$gt": { "$date": 165662640000 }
  }
}
  • Run mongodump by specifying the queryFile parameter using the following command:
mongodump --db <dbName> --collection <collectionName> --queryFile queryFile.json
SUB0DH
  • 5,130
  • 4
  • 29
  • 46