Pentaho MongoDB Report query date ranges

Question

I'm trying to generate a report using Pentaho Report Designer MongoDB native connector but I can't get it to work with date ranges.

My collection has a column named "timestamp", I tried to put the following in the query tab:

{"timestamp" : {$gte :ISODate("2013-12-04T15:10:00.000Z")}}
{"timestamp" : {$gte : new Date("2013-12-04T15:10:00.000Z")}}

while these queries both work using a .find(query) on the mongo shell, in the Pentaho Report Designer I get no rows and no error message.

Simpler queries like:

{"objType" : "Image"}

works fine in PRD.

EDIT:

I managed to get it work thanks to suggestions from:

http://wiki.pentaho.com/display/EAI/MongoDB+Input

So the answer is to use something like:

{ "$query" : { "timestamp" : { $gte : { $date : "2013-12-04T15:10:00Z" }, $lte : { $date : "2013-12-04T15:12:00Z" } } } }

Still I can't get it to work using Date parameters: If I put:

{ "$query" : { "timestamp" : { $gte : { $date : "${StartDate}" }, $lte : { $date : "${EndDate}" } } } }

In the query tab and I choose "StartDate" and "EndDate" to be of "Date" type it won't work. If I set the type of the paramters to "String" and set their value in ISO formats, it works correctly.

Is there a way to use the Date type for the parameters directly?

user_717267 · Answer

From https://help.pentaho.com/Documentation/8.2/Products/Data_Integration/Transformation_Step_Reference/MongoDB_Input

You can use something like next:

{ created_at : { $gte : { $date : "2014-12-31T00:00:00.000Z" } } }

With variables:

# my_date = '2014-12-31'
{ created_at : { $gte : { $date : "${my_date}T00:00:00.000Z" } } }

Or:

# my_date_time = '2014-12-31T00:00:00.000Z'
{ created_at : { $gte : { $date : "${my_date_time}" } } }

Pentaho MongoDB Report query date ranges

1 Answers