5

I am a new beginner of mongodb. I want to export some data in recent hours from my databases. So, I think I need to write mongoexport command and include date range in --query options to do it.

I write a bash file like this and try to run it:

#!/bin/bash

mongoexport --host localhost:27017 --db copy --collection txt --csv --fields x1,x2,x3...,date --query '{ "date" : {$gt:new Date(new Date() - 1000*60*60*3)} }' --out home/data.csv

But I get the results says:

connected to: localhost:27017
assertion: 16619 code FailedToParse: FailedToParse: Expecting '}' or ',': offset:25 of:{ "date" : {$gt:new Date(new Date() - 1000*60*60*3)} }

It sees connect to localhost but cannot output the data. If I remove --query option, this can run successfully and get the whole data, but I must need the query to subset the data in recently 3 hours.

Any ideas and help will be highly appreciated. Thank you and Best.

Jahid
  • 21,542
  • 10
  • 90
  • 108
Sophia
  • 47
  • 5
  • check this http://stackoverflow.com/questions/14758605/mongoexport-using-gt-and-lt-constraints-on-a-date-range – Abhay PS May 12 '15 at 20:16

1 Answers1

1

with mongoexport you have to provide the Date object a timestamp.

An explaination is answered here: MongoDb timestamp

What you can write as script is something like this (I'm rather rusty with bash, surely can be improved to stay on a single line): 

timestamp=$(date +%s)
let total=$timestamp*1000-3600*1000*3
mongoexport --host localhost:27017 --db copy --collection txt --csv --fields x1,x2,x3...,date --query '{ "date" : {$gt:new Date('$total')} }' --out home/data.csv
Community
  • 1
  • 1
Luca Foppiano
  • 157
  • 12
  • Hi Luca, Thank you for your help. I write the bash script like you say and there is no error right now. But I still not get the recent 3 hour datas. It like this:connected to: localhost:27017 exported 0 records. But I am sure the copy database on local is new one I copy from server some minutes ago, it may include recently datas. So, why I still get 0 record? – Sophia May 14 '15 at 02:54
  • Dear Sophia, to check you can connect to the collection and run a query db.txt.find({}, {_id: 0, ts:1}).sort({ts: -1}) db.txt.find({}, {_id: 0, ts:1}).sort({ts: 1}) and check the dates that you are getting out. Alternatively, another test would be to export using a query from new Date(0): `mongoexport --host localhost:27017 --db copy --collection txt --csv --fields x1,x2,x3...,date --query '{ "date" : {$gt: new Date(0)} }' --out home/data.csv ` Do you get anything? – Luca Foppiano May 14 '15 at 20:04
  • Hi Luca, thank you again. I enter mongo ,show dbs and show collections in copy database and use the code like you write, but it get error. show collections img system.indexes txt > db.txt.find({}, {_id: 0, ts:1}).sort({ts: -1}); error: { "$err" : "Runner error: Overflow sort stage buffered data usage of 33555446 bytes exceeds internal limit of 33554432 bytes", "code" : 17144 – Sophia May 15 '15 at 03:13
  • also, what is the mean of new Date(0)? – Sophia May 15 '15 at 03:14
  • To answer your questions: 1. My mistake, you have to use 'date' instead of 'ts'. `db.txt.find({}, {_id: 0, date:1}).sort({date: -1})` so you can see what is the last date, using sort({date: 1}) you will find the first date. In this way you can compare the date you are calculating with the two interval and see whether the script is not working. 2. Date(0) is the first date that the object can carry, it should be somewhere in 1970, enough to alow you to export some data (see http://stackoverflow.com/questions/7895171/java-date0-is-not-1-1-1970) Could you please write down steps in mongo? – Luca Foppiano May 15 '15 at 06:23
  • Hi Luca, thank you so much. I use db.txt.find({},{_id: 0,date:1}).sort({date: -1}) and get the last date is { "date" : ISODate("2015-05-14T13:58:12Z") }and the first date is in 1970. So, I think this range is normal.But when I use new Date(0) in --query option to test, like : mongoexport --host localhost:27017 --db copy --collection txt --csv --fields x1,x2,x3...,date --query '{ "date" : {$gt: new Date(0)} }' --out home/data.csv it still export 0 record. – Sophia May 15 '15 at 14:12
  • is there some problem of the --query '{"date" : {$gt: new Date()}}' syntax? I know there is JSON syntax in this query option. But I read on W3School website. they do not give any example about "date" syntax in JSON. So worry about this task. I have copy the database to local, finish R file statistical analysis, just cannot fill out how to load recent 3 hours data at last. – Sophia May 15 '15 at 14:35