Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: What is the correct Date Format for mongodb when using date parameters?

  1. #1
    Join Date
    Jan 2013
    Posts
    101

    Default What is the correct Date Format for mongodb when using date parameters?

    Hi,
    Posted in the kettle section of forum instead of reporting Reposting.

    I'm using date parameters in PRD 5 that I used before and the format isn't working when querying mongodb.

    here is my json

    { time: {$gte: {$date : "${startDate}"}, $lte: {$date: "${stopDate}"}}, host : "${HostName}", type_instance: "${Counter}" }



    The non date parameters work fine. Here is the format I've always used for Mongodb date paramters. Is there something different in prd 5?

    EEE MMM dd HH:mm:ss zzz yyyy

    Any help would be appreciated.

    Mike

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    According the MongoDB documentation and this blog post, your date should never have worked in the first place.

    MongoDB wants to have the dates as a 64-bit signed integer for milliseconds since epoch UTC. (Basically what you would feed into the date-constructor in JavaScript).

    In PRD there is no easy step to convert dates to epoch-milliseconds, so you will have to resort to some basic scripting here (using BeanShell)

    Code:
    import java.util.Date;
    
    Object getValue() {
       Object maybeDate = dataRow.get("your-date-field");
       if (maybeDate instanceof Date == false) return null;
       Date d = (Date) maybeDate;
       return d.getTime(); 
    }
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jan 2013
    Posts
    101

    Default

    Hi,

    Thanks for the reply. I've been using Groovy and it works for date parameters using this format (EEE MMM dd HH:mm:ss zzz yyyy) but the issue is I have values stored as arrays and in groovy and I'm having a heck of a time pulling those values out into cursor. I'm trying the [] to pull out value position. It's stored like this: "values" : [1.21, 1.81, 1.82]. If I could pull those values out i can just continue to use groovy for mongodb. I really like the new PRD (JSON interpreter)as it already does the work for you and pulls the values out of the array storage. Any suggestions on the groovy script to pull those values out?

    def docs = meter.find(query).sort(sort).limit(25)
    while (docs.hasNext()) {
    def doc = docs.next()
    model.addRow([ doc.get("type_instance"), doc.get("values[0]"), doc.get("time"),doc.get("host")] as Object[]);
    }

    Currently using PDI in 4.8 suite to pull values out but I have to run etl instead of just pulling values by date window through scripting.



    Mike
    Last edited by arcelio1023; 11-21-2013 at 12:04 PM. Reason: further details

  4. #4
    Join Date
    Jan 2012
    Posts
    5

    Default

    Actually I made some tests using PRD 5 CE: if you use String as the parameter type, something like:

    Code:
    { "$query" : { "timestamp" : { $gte : { $date : "${StartDate}" }, $lte : { $date : "${EndDate}" } } } }
    in the query tab, works fine.

    However I can't get it to work with "Date" parameter type. Any suggestions?

    EDIT:

    The only way I found to make it work is to create a new libformula that performs the "opposite" of PARSEDATE. That is: using a SimpleDateFormat to format a Date object into a UTC String as Mongo expects it ("yyyy-MM-dd'T'HH:mm:ssX") and setting the TimeZone to GMT (as Mongo stores date in UTC). The date parameter is set to use a data picker and the client timezone, in this way I use a hidden String parameter that takes the date type parameter in order to execute the formula and then be substitued in the Mongo query.

    I don't know if I'm missing something but I think that it is quite common to have date parameters in reports, so if this is the only way to have a Parameter of "Date" type inserted in a Mongo query, probably we should fill a JIRA for it...
    Last edited by laider; 12-06-2013 at 05:31 AM. Reason: Additional information provided

  5. #5
    Join Date
    Jan 2013
    Posts
    101

    Default

    Can you give me an example of the formula? Where would I put the libformula, in Default Value formula or Post-processing formula?

  6. #6
    Join Date
    Jan 2012
    Posts
    5

    Default

    First of all, follow the guide:


    http://wiki.pentaho.com/display/Repo...r+own+function


    in order to know how to build your JAR in order to expand the libformula.


    The formula code is very simple:


    FormatDate.java:


    Code:
    package extensions.formula;
    
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.TimeZone;
    
    
    import org.pentaho.reporting.libraries.formula.EvaluationException;
    import org.pentaho.reporting.libraries.formula.FormulaContext;
    import org.pentaho.reporting.libraries.formula.LibFormulaErrorValue;
    import org.pentaho.reporting.libraries.formula.function.Function;
    import org.pentaho.reporting.libraries.formula.function.ParameterCallback;
    import org.pentaho.reporting.libraries.formula.lvalues.TypeValuePair;
    import org.pentaho.reporting.libraries.formula.typing.Type;
    import org.pentaho.reporting.libraries.formula.typing.coretypes.TextType;
    
    
    
    
    /**
    * The FormatDate function is used to convert Date objects into String using a
    * SimpleDateFormat. The format String and the time zone can be provided as
    * optional parameters, default values are respectively:
    * "yyyy-MM-dd'T'HH:mm:ssX" and "GMT".
    * 
    * For further information: {@see java.util.SimpleDateFormat} and {@see
    * java.util.TimeZone}.
    * 
    * 
    */
    public class FormatDate implements Function {
        private static final long serialVersionUID = 235681519407842338L;
        private static final String DEFAULT_FORMAT = "yyyy-MM-dd'T'HH:mm:ssX";
        private static final String DEFAULT_TIMEZONE = "GMT";
    
    
    
    
        public FormatDate() {
    
    
    
    
        }
    
    
    
    
        @Override
        public TypeValuePair evaluate(FormulaContext context,
                ParameterCallback parameters) throws EvaluationException {
            Date date;
            SimpleDateFormat sdf;
            String formatString = DEFAULT_FORMAT;
            String timezoneString = DEFAULT_TIMEZONE;
    
    
    
    
            // Checks the number of paramters
            final int parameterCount = parameters.getParameterCount();
            if (parameterCount < 1 || parameterCount > 3) {
                throw new EvaluationException(
                        LibFormulaErrorValue.ERROR_ARGUMENTS_VALUE);
            }
    
    
    
    
            // Gets the first parameter (i.e. the Date)
            final Type typeDate = parameters.getType(0);
            final Object valueDate = parameters.getValue(0);
            date = context.getTypeRegistry().convertToDate(typeDate, valueDate);
    
    
    
    
            // If null: error!
            if (date == null) {
                throw new EvaluationException(
                        LibFormulaErrorValue.ERROR_INVALID_ARGUMENT_VALUE);
            }
    
    
    
    
            // Parses other parameters (if provided)
            if (parameterCount > 1) {
                // Gets the first parameter (i.e. the Date)
                final Type typeFormat = parameters.getType(1);
                final Object valueFormat = parameters.getValue(1);
                formatString = context.getTypeRegistry().convertToText(typeFormat,
                        valueFormat);
            }
            if (parameterCount > 2) {
                // Gets the first parameter (i.e. the Date)
                final Type typeTimeZone = parameters.getType(2);
                final Object valueTimeZone = parameters.getValue(2);
                timezoneString = context.getTypeRegistry().convertToText(
                        typeTimeZone, valueTimeZone);
            }
    
    
    
    
            // Creates SimpleDateFormat sets TimeZone and formats date
            try {
                sdf = new SimpleDateFormat(formatString);
                sdf.setTimeZone(TimeZone.getTimeZone(timezoneString));
                String formattedDate = sdf.format(date);
                
                // Builds and returns result
                TypeValuePair result = new TypeValuePair(TextType.TYPE, formattedDate);
                
                return result;
            } catch (Exception e) {
                // If any exception is thrown, it means that one of the aruments is invalid.
                throw new EvaluationException(
                        LibFormulaErrorValue.ERROR_INVALID_ARGUMENT_VALUE);
            }
        }
    
    
    
    
        @Override
        public String getCanonicalName() {
            return "FORMATDATE";
        }
    }

    FormatDateDescription.java:


    Code:
    package extensions.formula;
    
    
    import org.pentaho.reporting.libraries.formula.function.AbstractFunctionDescription;
    import org.pentaho.reporting.libraries.formula.function.FunctionCategory;
    import org.pentaho.reporting.libraries.formula.function.datetime.DateTimeFunctionCategory;
    import org.pentaho.reporting.libraries.formula.typing.Type;
    import org.pentaho.reporting.libraries.formula.typing.coretypes.DateTimeType;
    import org.pentaho.reporting.libraries.formula.typing.coretypes.TextType;
    
    
    /**
     * Describes the FormatDate function.
     * 
     */
    public class FormatDateDescription extends AbstractFunctionDescription {
    	private static final long serialVersionUID = 3400709085062526432L;
    
    
    	public FormatDateDescription() {
    		super("FORMATDATE",
    				"extensions.formula.FormatDate");
    	}
    
    
    	@Override
    	public FunctionCategory getCategory() {
    		return DateTimeFunctionCategory.CATEGORY;
    	}
    
    
    	@Override
    	public int getParameterCount() {
    		return 3;
    	}
    
    
    	@Override
    	public Type getParameterType(int position) {
    		switch (position) {
    		case 0:
    			return DateTimeType.DATETIME_TYPE;
    		default: // Others
    			return TextType.TYPE;
    		}
    	}
    
    
    	@Override
    	public Type getValueType() {
    		return TextType.TYPE;
    	}
    
    
    	@Override
    	public boolean isParameterMandatory(int position) {
    		switch (position) {
    		case 0:
    			return true;
    		default: // Only first parameter is mandatory
    			return false;
    		}
    	}
    }

    The FormatDate.properties:


    Code:
    display-name=FORMATDATE
    description=Formats a Date into a String using a SimpleDateFormatter
    parameter.0.display-name=Date
    parameter.0.description=The Date object to be formatted
    parameter.1.display-name=Format
    parameter.1.description=The string to be passed to the simple date format. If not specified converts to UTC.
    parameter.2.display-name=TimeZone
    parameter.2.description=The Timezone to be passed to the simple date format. If not specified uses GMT.

    libformula.properties:


    Code:
    org.pentaho.reporting.libraries.formula.functions.datetime.FormatDate.class=extensions.formula.FormatDate
    org.pentaho.reporting.libraries.formula.functions.datetime.FormatDate.description=extensions.formula.FormatDateDescription

    After that in you Mongo report you will have a regular "Date" parameter (let's call it "StartDate") and then you will need a (hidden) parameter of String type whose post-processing formula will be =FORMATDATE([StartDate]).


    Hope it helps.


    Regards

  7. #7
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Actually, I do have a new SDK that contains a ready-to-use module for formula functions as well as all necessary test-cases to make it as easy as possible to create new formula function implementations.

    https://github.com/tmorgner/pentaho-...rmula-function
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  8. #8
    Join Date
    Jan 2013
    Posts
    101

    Default

    Ok It's been a while since I posted this. Still haven't been able to get the date format to work with Mongodb. I've referenced the formula and can't get it to work. When I was using 4.8 PRD al I had to do was format the date in the date picker. In 5.1 PRD that format doesn't work anymore.

  9. #9
    Join Date
    Nov 2011
    Posts
    7

    Default

    Quote Originally Posted by arcelio1023 View Post
    Ok It's been a while since I posted this. Still haven't been able to get the date format to work with Mongodb. I've referenced the formula and can't get it to work. When I was using 4.8 PRD al I had to do was format the date in the date picker. In 5.1 PRD that format doesn't work anymore.
    Not sure if you figured this out, but after some tests, I think what you need to do is use the ISODate("...") format
    Code:
    db.collection.find({date: {$gte:ISODate("2014-06-14 00:00:00Z"),$lt:ISODate("2014-06-17 00:00:00Z)}})
    works great for me.

  10. #10
    Join Date
    Apr 2012
    Posts
    6

    Default

    Quite old post on this topic, I am working on community 5.4, Still facing the issues with the date prompt in MongoDb.
    Please If any one has a solution to this issue can reply and guide how it can work with date prompt.
    Any help would be appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.