FilterBuilder and Date, DateTime, CreateorTimeStamp or ModifierTimestamp

[jperin]

Hi Isomorphoic,

Version : Isomorphic SmartClient/SmartGWT Framework (v9.1p_2014-06-03/PowerEdition Deployment 2014-06-03)

I have a question about the FilterBuilder and DateTime.

I use the format “yyyy/MM/dd - HH:mm:ss” for datetime/timestamp in my application.

The column of type DateTime, CreatorTimeStamp and ModifierTimeStamp are display in this format to avoid showing the milliseconds to the user. But these milliseconds are stored in the database.

1. I would like to use a RelativeDateItem without time in the FilterBuilder. I try to do like this

RelativeDateItem dateItem = new RelativeDateItem();
dateItem.setShowPickerTimeItem(false);
dateItem.setFormat("yyyy/MM/dd");
dateItem.setDateParser(new DateParser() {
					
	@Override
	public Date parse(String dateString) {
		//parse yyyy/MM/dd
		return Localization.parseDate(dateString);
	}
});
dateItem.setDateFormatter(new DateDisplayFormatter() {
					
	@Override
	public String format(Date date) {
		//format yyyy/MM/dd
		return Localization.formatDate(date);
	}
});
dataSourceField.setEditorProperties(dateItem);

When I select/enter a date in the RelativeDateItem, example 2014/05/15 and I do the filtering. The criteria is like this

{
    "_constructor":"AdvancedCriteria", 
    "operator":"and", 
    "criteria":[
        {
            "fieldName":"CREATED", 
            "operator":"equals", 
            "value":new Date(1400104800000), 
            "_constructor":"AdvancedCriteria"
        }
    ]
}

The payload

=== 2014-06-04 15:10:26,348 [0-21] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"CREATED",
                operator:"equals",
                value:new Date(1400104800000),
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"businessUnit",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListModuleView_10_0",
    useStrictJSON:true,
    appID:"builtinApplication",
    operation:"businessUnit_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"CREATED",
                operator:"equals",
                value:new Date(1400104800000),
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}

The sql generated is

((SEC_BUSINESS_UNIT.CREATED = '2014-05-15T00:00:00' AND SEC_BUSINESS_UNIT.CREATED IS NOT NULL))

The definition of the column in the database (SQLServer) is CREATED DATETIME DEFAULT GETDATE() NOT NULL

2. Where are the milliseconds, as the column is type “CreatorTimeStamp”

3. As the column is type “CreatorTimeStamp”, when I select the equals operator with date value 2014/05/15, this must be ‘translated’ in between 2014/05/15 00:00:00.000 and 2014/05/15 23:59:59.999. Can it be done automatically or must I have to code that.

Regards

Julien

[Isomorphic]

Re: #1 and #3, what we'd recommend is just using the control in its default mode. If the user wants to select a particular day, this is easily achieved with the "between" operators, which then leaves the "equals" operator usable for selecting an exact time instead of making it redundant with "between".

One problem with trying to specify criteria as just a date when the field is datetime is that, even if the criteria were automatically treated as equivalent to a "between" operator, the server and client timezone may differ, causing different date ranges to be selected.

We're still looking into #2.

[jperin]

Hi
I'm totally agree with your for point #1 and #3.

Any news for the point #2?

[Isomorphic]

Please read the doc for DataSource.setTrimMilliseconds(), in particular the bit that starts "The SQLDataSource was designed for accuracy to the nearest second, and making it support millisecond accuracy requires a couple of steps" - let us know if you still have questions.

[jperin]

Thanks for your quick answer.
I will check for this.

However, I think I found another issue on the filterbuilder.
Here is as sample code

	@Override
	public void onModuleLoad() {
		
		DateUtil.setNormalDatetimeDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return dateTimeFormat.format(date);
			}
		});
		DateUtil.setNormalDateDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return dateFormat.format(date);
			}
		});
		DateUtil.setNormalTimeDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return timeFormat.format(date);
			}
		});

		DateUtil.setShortDatetimeDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return dateTimeFormat.format(date);
			}
		});
		DateUtil.setShortDateDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return dateFormat.format(date);
			}
		});
		DateUtil.setShortTimeDisplayFormatter(new DateDisplayFormatter() {

			public String format(Date date) {
				return timeFormat.format(date);
			}
		});

		DateUtil.setDateParser(new DateParser() {

			public Date parse(String dateString) {
				
				if ((dateString == null) || (dateString.length() == 0)) 
					return null;
				try{ 
					return dateTimeFormat.parse(dateString);
				}catch(Exception e ){
					e.printStackTrace();
				}
				try{
					return timeFormat.parse(dateString);
				}catch(Exception e){
					e.printStackTrace();
				}
				try {
					return dateFormat.parse(dateString);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});

		
		DataSource dataSource = new DataSource();
		dataSource.setClientOnly(true);
		
		DataSourceField nameField = new DataSourceField("Name",FieldType.TEXT);
		DataSourceField dateTimeField = new DataSourceField("DateTime",FieldType.DATETIME);
		DataSourceField creatordField = new DataSourceField("CreatorTimeStamp",FieldType.CREATORTIMESTAMP);
		
		dataSource.setFields(nameField,dateTimeField,creatordField);
		
		Map<String,Object> properties = new HashMap<String, Object>();
		properties.put("Name","Test 1");
		properties.put("DateTime",new Date());
		properties.put("CreatorTimeStamp",new Date());
dataSource.addData(new Record(properties));

		properties = new HashMap<String, Object>();
		properties.put("Name","Test 2");
		Date date = new Date();
		date.setHours(23);
		date.setMinutes(59);
		date.setSeconds(40);
		properties.put("DateTime",date);
		properties.put("CreatorTimeStamp",new Date());
		dataSource.addData(new Record(properties));		
		
		final ListGrid grid = new ListGrid();
		grid.setDataSource(dataSource);
		grid.setWidth(550);  
		grid.setAutoFetchData(true);  
		grid.setHeight(224);  

		
		ListGridField nameGridField = new ListGridField("Name","Name");
		ListGridField dateTimeGridField = new ListGridField("DateTime","Date Time",100);
		ListGridField creatordGridField = new ListGridField("CreatorTimeStamp","Creator TimeStamp",200);
		
		grid.setFields(nameGridField,dateTimeGridField,creatordGridField);
		
		DataSource dataSource2 = grid.getDataSource();
		
		List<DataSourceField> fields = new ArrayList<DataSourceField>();
		for(DataSourceField field : dataSource2.getFields()){
			System.out.println(field.getName() +" "+field.getClass().getName());
			DataSourceField dataSourceField = new DataSourceField(field.getJsObj());
			if(field.getType() == FieldType.TEXT || field.getType() == FieldType.NTEXT){
				 dataSourceField.setValidOperators(
						 OperatorId.EQUALS, OperatorId.NOT_EQUAL, OperatorId.IEQUALS, OperatorId.INOT_EQUAL,
						 OperatorId.STARTS_WITH, OperatorId.NOT_STARTS_WITH, OperatorId.ISTARTS_WITH, OperatorId.INOT_STARTS_WITH,
						 OperatorId.ENDS_WITH, OperatorId.NOT_ENDS_WITH, OperatorId.IENDS_WITH, OperatorId.INOT_ENDS_WITH,
						 OperatorId.CONTAINS, OperatorId.NOT_CONTAINS, OperatorId.ICONTAINS, OperatorId.INOT_CONTAINS,
						 OperatorId.REGEXP,OperatorId.IREGEXP,
						 OperatorId.NOT_NULL);
			
			}else if(field.getType() == FieldType.CREATORTIMESTAMP || field.getType() == FieldType.MODIFIERTIMESTAMP
					|| field.getType() == FieldType.DATETIME || field.getType() == FieldType.DATE){
				dataSourceField.setValidOperators(
						OperatorId.BETWEEN, OperatorId.BETWEEN_INCLUSIVE,
						OperatorId.GREATER_OR_EQUAL, OperatorId.GREATER_THAN,
						OperatorId.LESS_OR_EQUAL, OperatorId.LESS_THAN,
						OperatorId.NOT_NULL);
			}
			fields.add(field);
		}
				
		final FilterBuilder builder = new FilterBuilder();
		builder.setDataSource(dataSource);


        IButton filterButton = new IButton("Filter");
        filterButton.addClickHandler(new ClickHandler() {
			
			@Override
			public void onClick(ClickEvent event) {
				grid.filterData(builder.getCriteria());  
			}
		});
        IButton clearfilterButton = new IButton("Clear Filter");
        clearfilterButton.addClickHandler(new ClickHandler() {
			
			@Override
			public void onClick(ClickEvent event) {
				grid.fetchData();
			}
		});
		
		VStack vStack = new VStack(10);  
        vStack.addMember(builder);  
        vStack.addMember(filterButton);  
        vStack.addMember(clearfilterButton); 
        vStack.addMember(grid);  
  
        vStack.draw();  

		
	}

This is a sample code with a DataSource with 3 fields.
-Text
-DateItem
-CreatorTimestamp

Observations :
1. If you filter on the date time column with operation between (match case) or between (inclusive, match case) and you let the default value for start and end to "today".
You can see just near the calendar icon the display of the date. The display are "10/06/2014 00:00:00" to "10/06/2014 23:59:59".
--> The filter succeed

2. Now if you filter on the date time column with operation between (match case) or between (inclusive, match case), instead of let today you select with the calendar the current day. The display are "10/06/2014 00:00:00" to "10/06/2014 23:59:00".
--> Where the second are gone? The filter return only 1 row.
I try to found a way to set the second in the calendar too, but I did not found a solution.

3. Now try to filter on the CreatorTimestamp column, with operation between (match case) and let the value to today. Once the filter is executed, no records are display in the grid. If you use the between (inclusive, match case) with today and filter, the grid is filtered and the two records are still display.
-->I think there is an issue with this, maybe with time.
- The same appears if you select the current day in the calendar.
- If you enter a time, this does not work too


Regards

Julien

[Isomorphic]

You can use DateChooser.setShowSecondItem(Boolean) to have the timeItem allow picking of seconds (via relativeDateItem.setPickerProperties(DateChooser) from an override of FilterBuilder.getValueFieldProperties()).

That will most likely address your other issues too - for #3, take a look at the SQL being generated and let us know what you find.

[jperin]

I'm using the Version : Isomorphic SmartClient/SmartGWT Framework (v9.1p_2014-06-03/PowerEdition Deployment 2014-06-03)

I did not found setPickerProperties(DateChooser) on the RelativeDateItem.

Did you have an example?


There is no Sql generated as the Datasource in the sample is client only mode and in my sever the grid do not call the server to filter has the method willFetchData() return false. (I have only 20 records actually).

When I was testing the equals see my first thread of this post, you will see the sql

[Isomorphic]

Ok, we'll take a look - in the meantime, you can use setPickerTimeItemProperties(), passing in a TimeItem with setShowSecondItem(true)

[jperin]

Il have the setPickerTimeItemProperties() like this

 if(field.getType() == FieldType.CREATORTIMESTAMP || field.getType() == FieldType.MODIFIERTIMESTAMP|| field.getType() == FieldType.DATETIME){
dataSourceField.setValidOperators(
	OperatorId.BETWEEN, OperatorId.BETWEEN_INCLUSIVE,
	OperatorId.GREATER_OR_EQUAL, OperatorId.GREATER_THAN,
	OperatorId.LESS_OR_EQUAL, OperatorId.LESS_THAN,
	OperatorId.NOT_NULL);
			
	RelativeDateItem item = new RelativeDateItem();
	TimeItem timeItem = new TimeItem();
	timeItem.setShowSecondItem(true);
	item.setPickerTimeItemProperties(timeItem);
				
	dataSourceField.setEditorProperties(item);
				
}

This solve the point 2. The second are well present in the criteria.
Thanks for this information.

I will wait for information about the filter on column type CreatorTimestamp...

[jperin]

Any news for the filter on column type CreatorTimeStamp

[Isomorphic]

We can't run your sample because it uses dateFormat, timeFormat and datetimeFormat but doesn't set them up.

Please show the code that sets those up.

[jperin]

oops sorry

Here they are

private static DateTimeFormat dateTimeFormat = DateTimeFormat.getFormat("dd MMM yyyy - HH:mm:ss");
private static DateTimeFormat dateFormat = DateTimeFormat.getFormat("dd MMM yyyy");
private static DateTimeFormat timeFormat  = DateTimeFormat.getFormat("HH:mm:ss");

[jperin]

Any news about this?

[Isomorphic]

Not yet, but we have someone looking. We'll follow up when we have more information for you

Regards
Isomorphic Software

[Isomorphic]

This has been fixed in 4.1 - please retest with a build dated June 21 or later.