Mongodump --query not able to filter using timestamp

Working with Data Developer Tools
,
1

mongodump --host=“rs0/localhost:27017,localhost:27018,localhost:27019” --readPreference=secondary -d local -c oplog.rs --query="{“ts”:{"$gte":"$Timestamp(0000000000,50)"}}" -vvv -o /home/anupama/backupec2

I am getting output

writing local.oplog.rs to
2020-03-29T08:37:08.134+0530 not counting query on local.oplog.rs
2020-03-29T08:37:08.328+0530 done dumping local.oplog.rs (0 documents)
2020-03-29T08:37:08.328+0530 ending dump routine with id=0, no more work to do
2020-03-29T08:37:08.328+0530 dump phase III: the oplog
2020-03-29T08:37:08.328+0530 finishing dump

But when try to dump without using query it dumps the file

2

I think you have to escape $ sign
or use single quote for json query

1 Like
3

mongodump --host=“rs0/localhost:27017,localhost:27018,localhost:27019” --readPreference=secondary -d local -c oplog.rs --query=“{“ts”:{”$gte":“$Timestamp(0000000000,50)”}}" -vvv -o /home/anupama/backupec2

The query is to be formatted according to the mongodump’s Extended JSON v2. This will work with Windows system (e.g.,):

"{ \"ts\":{ \"$gte\": { \"$timestamp\": { \"t\": 1565545664, \"i\": 1 } } } }"

The backslash (\) character is to escape the double-quote (") within the outer quotes.

2 Likes
4

Thanks for the update but when i tried it on startup_log give below

done dumping local.startup_log (0 documents)
2020-03-30T10:00:32.595+0530 finishing dump

Tried lt,gt etc Gives same result
In your case did it dump rows?

5

Please post couple of sample input documents.

6

Will this help?

> db.getCollection('startup_log').find().limit(2).pretty()
{
        "_id" : "DESKTOP-RIP97MG-1547099484533",
        "hostname" : "DESKTOP-RIP97MG",
        "startTime" : ISODate("2019-01-10T05:51:24Z"),
        "startTimeLocal" : "Thu Jan 10 11:21:24.533",
        "cmdLine" : {
                "config" : "C:\\Program Files\\MongoDB\\Server\\4.0\\bin\\mongod.cfg",
                "net" : {
                        "bindIp" : "127.0.0.1",
                        "port" : 27017
                },
                "service" : true,
                "storage" : {
                        "dbPath" : "C:\\Program Files\\MongoDB\\Server\\4.0\\data",
                        "journal" : {
                                "enabled" : true
                        }
                },
                "systemLog" : {
                        "destination" : "file",
                        "logAppend" : true,
                        "path" : "C:\\Program Files\\MongoDB\\Server\\4.0\\log\\mongod.log"
                }
        },
        "pid" : NumberLong(13372),
        "buildinfo" : {
                "version" : "4.0.5",
                "gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412",
                "targetMinOS" : "Windows 7/Windows Server 2008 R2",
                "modules" : [ ],
                "allocator" : "tcmalloc",
                "javascriptEngine" : "mozjs",
                "sysInfo" : "deprecated",
                "versionArray" : [
                        4,
                        0,
                        5,
                        0
                ],
                "openssl" : {
                        "running" : "Windows SChannel"
                },
                "buildEnvironment" : {
                        "distmod" : "2008plus-ssl",
                        "distarch" : "x86_64",
                        "cc" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24223 for x64",
                        "ccflags" : "/nologo /EHsc /W3 /wd4355 /wd4800 /wd4267 /wd4244 /wd4290 /wd4068 /wd4351 /wd4373 /we4013 /we4099 /we4930 /WX /errorReport:none /MD /O2 /Oy- /bigobj /utf-8 /Zc:rvalueCast /Zc:strictStrings /volatile:iso /Gw /Gy /Zc:inline",
                        "cxx" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24223 for x64",
                        "cxxflags" : "/TP",
                        "linkflags" : "/nologo /DEBUG /INCREMENTAL:NO /LARGEADDRESSAWARE /OPT:REF",
                        "target_arch" : "x86_64",
                        "target_os" : "windows"
                },
                "bits" : 64,
                "debug" : false,
                "maxBsonObjectSize" : 16777216,
                "storageEngines" : [
                        "devnull",
                        "ephemeralForTest",
                        "mmapv1",
                        "wiredTiger"
                ]
        }
}
{
        "_id" : "DESKTOP-RIP97MG-1547362502494",
        "hostname" : "DESKTOP-RIP97MG",
        "startTime" : ISODate("2019-01-13T06:55:02Z"),
        "startTimeLocal" : "Sun Jan 13 12:25:02.494",
        "cmdLine" : {
                "config" : "C:\\Program Files\\MongoDB\\Server\\4.0\\bin\\mongod.cfg",
                "net" : {
                        "bindIp" : "127.0.0.1",
                        "port" : 27017
                },
                "service" : true,
                "storage" : {
                        "dbPath" : "C:\\Program Files\\MongoDB\\Server\\4.0\\data",
                        "journal" : {
                                "enabled" : true
                        }
                },
                "systemLog" : {
                        "destination" : "file",
                        "logAppend" : true,
                        "path" : "C:\\Program Files\\MongoDB\\Server\\4.0\\log\\mongod.log"
                }
        },
        "pid" : NumberLong(3796),
        "buildinfo" : {
                "version" : "4.0.5",
                "gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412",
                "targetMinOS" : "Windows 7/Windows Server 2008 R2",
                "modules" : [ ],
                "allocator" : "tcmalloc",
                "javascriptEngine" : "mozjs",
                "sysInfo" : "deprecated",
                "versionArray" : [
                        4,
                        0,
                        5,
                        0
                ],
                "openssl" : {
                        "running" : "Windows SChannel"
                },
                "buildEnvironment" : {
                        "distmod" : "2008plus-ssl",
                        "distarch" : "x86_64",
                        "cc" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24223 for x64",
                        "ccflags" : "/nologo /EHsc /W3 /wd4355 /wd4800 /wd4267 /wd4244 /wd4290 /wd4068 /wd4351 /wd4373 /we4013 /we4099 /we4930 /WX /errorReport:none /MD /O2 /Oy- /bigobj /utf-8 /Zc:rvalueCast /Zc:strictStrings /volatile:iso /Gw /Gy /Zc:inline",
                        "cxx" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24223 for x64",
                        "cxxflags" : "/TP",
                        "linkflags" : "/nologo /DEBUG /INCREMENTAL:NO /LARGEADDRESSAWARE /OPT:REF",
                        "target_arch" : "x86_64",
                        "target_os" : "windows"
                },
                "bits" : 64,
                "debug" : false,
                "maxBsonObjectSize" : 16777216,
                "storageEngines" : [
                        "devnull",
                        "ephemeralForTest",
                        "mmapv1",
                        "wiredTiger"
                ]
        }
}
>
7

–query=“{“ts”:{”$gte":“$Timestamp(0000000000,50)”}}

So, where is the field ts in the documents? Are you querying on some other field?

Please check the documents and the field you are querying upon - the field name must be same.

8

I tried with startTime but still same result

9

“startTime” : ISODate(“2019-01-13T06:55:02Z”) is not is timestamp data type; it is Date data type.

For your case, use this format with the correct date string:

--query "{ \"startTime\":{ \"$gte\": { \"$date\": \"2020-02-14T04:07:34Z\" } } }"

4 Likes
10

Thank you very much

It worked with date format

2020-03-30T11:55:05.934+0530 done dumping local.startup_log (4 documents)
2020-03-30T11:55:05.934+0530 dump phase III: the oplog
2020-03-30T11:55:05.936+0530 finishing dump

11

worked thanks for replying

12

Now this has a solved answer, when would you want to use a timestamp over a normal date/time ?

Thanks for any enlightenment :slight_smile:

13

Hello @NeilM, the timestamp data type is mostly for MongoDB internal usage. The Date data type is what is to be used for application data. Also see: BSON Types - Timestamp.

14

@Prasad_Saya

Thank you for the reply. I had read about it being for internal use/oplog etc, I was was just wondering if there was a situation where the timestamp field would be useful.

I wasn’t aware of it as a field type before, that’s all.

Thanks.

15

@NeilM, I haven’t come across such timestamp use case, but here is a discussion about to use or not to use:

1 Like
16

@Prasad_Saya

Thanks, the link helps highlighting the limited situation where it would be useful.

Cheers.

1 Like
18

Hi Ramachandra,

I have also tried the given Syntax
–query “{“ts”:{ “$gte”: { “$date”: “2022-04-21T04:07:34Z”}}}” But i am getting 0 results

2022-04-22T03:48:21.818-0500 writing local.oplog.rs to /ank/data/oplog_rs_1/local/oplog.rs.bson
2022-04-22T03:48:24.764-0500 local.oplog.rs 0
2022-04-22T03:48:25.678-0500 local.oplog.rs 0
2022-04-22T03:48:25.678-0500 done dumping local.oplog.rs (0 documents)

19

You have to use $timestamp
My query was for startup_log not oplog

Please check Prasad_Saya reply above
What is your os?
If it is windows try the query he has given above

20

A bit late but managed to query using shell date like this

–query “{“TABLENAME”:{”$gt":{"$date":"date -v-1H +"%Y-%m-%dT%H:%M:%S%z""}}}"

where “1H” stands for an hour, “1d” for days

22

@Prasad_Saya @Ramachandra_Tummala

I have tried this but 0 records. did i miss something ?

mongoexport.exe --port=27018 --username=user --password=%PS% --db=AlteryxGallery --collection=auditEvents --query="{ “startTime”:{ “$gte”: { “$date”: “2022-09-01T00:00:00.000Z” } } }"
2022-09-15T17:09:58.121+0100 connected to: mongodb://localhost:27018/
2022-09-15T17:09:58.127+0100 exported 0 records