topic Re: Query FeatureLayer by date in ArcGIS JavaScript Maps SDK Questions

Query FeatureLayer by date

JimWharton — Tue, 29 Apr 2014 12:42:01 GMT

I have a featurelayer with a field with "type: esriFieldTypeDate". I have a form on a page using the standard HTML5 date input type. (it uses the browser's built in datepicker) The format that this returns is in this format: '2014-04-29'. When I try to send this in a query, I get an error: "Unable to perform query. Please check your parameters."

What type of date format should I send in a query/querytask to be able to select dates before/after a chosen date?

I've tried using a javascript new Date(). I've tried sending it as 'YYYY-MM-DD', but nothing else.

Re: Query FeatureLayer by date

JakeSkinner — Sun, 12 Dec 2021 02:52:15 GMT

Hi Jim,

Try the following:

var date = "2014-04-29"; 
date = new Date(date); 
date = (date.getMonth()+1) + "/" + (date.getDate()+1) + "/" + date.getFullYear(); 

//returns '4/29/2014'

query.where = "EnteredDate = date '" + date + "'";

Re: Query FeatureLayer by date

JimWharton — Sun, 12 Dec 2021 02:52:18 GMT

Thanks!

Ok, so if slashed dates are the required format, I could do this:

var date = '2014-04-29'.split('-');
var esriDate = parseInt(date[1], 10) + '/' + parseInt(date[2], 10) + '/' + date[0];

Re: Query FeatureLayer by date

JimWharton — Tue, 29 Apr 2014 14:09:49 GMT

Ok, sadly, that still is giving me an error. My query params are as follows:

f:json
where:time > '2/4/2014'
returnGeometry:true
spatialRel:esriSpatialRelIntersects
outFields:*
outSR:102100

If I omit any dates from my search and search on things like 'username'. (" username LIKE '%"+username+"%'") My searches work just fine. It's only the dates that fail.

Re: Query FeatureLayer by date

JakeSkinner — Tue, 29 Apr 2014 14:30:36 GMT

Try the following:

where:time > date '2/4/2014'

Re: Query FeatureLayer by date

JimWharton — Sun, 12 Dec 2021 02:52:21 GMT

Interesting, how do I get that when writing a query?

Currently, I'm setting it up like this:

        esriDate: function(date) {
          var dateArr = date.split('-');
          dateArr.push(dateArr.shift());
          return dateArr.map(function(el) { return parseInt(el, 10); } ).join('/');
        },
    
        buildQuery: function() {
          var query = [];
          var name = this.username.value;
          var startDate = this.startDate.value;
          var endDate = this.endDate.value;

          if(name) {
            query.push("username LIKE '%"+name+"%'");
          }

          if(startDate) {
            query.push("time > '"+this.esriDate(startDate)+"'");
          }

          if(endDate) {
            query.push("time < '"+this.esriDate(endDate)+"'");
          }

          if(query.length > 1) {
            return query.join(' AND ');
          } else {
            return query.join('');
          }
        },

        submitQuery: function(e) {
          e.preventDefault();
          var query = new Query();
          query.returnGeometry = true;
          query.where = this.buildQuery(); 
          this.layer.selectFeatures(query, this.layer.SELECTION_NEW, function(data) {
            console.log(data);
            // var extent = graphicsUtils.graphicsExtent(data);
            // map.setExtent(extent, true);
          });
          menus.closeFeatureQuery();
          this.destroy();
        }

The query looks "mostly" SQLish. How do I specify that it's a date?

Re: Query FeatureLayer by date

JakeSkinner — Sun, 12 Dec 2021 02:52:23 GMT

Try this:

if(startDate) {
    query.push("time > date'"+this.esriDate(startDate)+"'");
  }

  if(endDate) {
    query.push("time < date'"+this.esriDat(endDate)+"'");
  }

Re: Query FeatureLayer by date

JimWharton — Tue, 29 Apr 2014 14:39:21 GMT

Ok, so we're casting it as some sort of date type? Can I cast dates in the original format? (YYYY-MM-DD) or do they need to be in the mm/dd/yyyy format before they're casted?

That just changes my query parameters to look like this:

f:json
where:time > date'2/4/2014'
returnGeometry:true
spatialRel:esriSpatialRelIntersects
outFields:*
outSR:102100

It still fails.

What is the acceptable way to query features by date? I haven't found any examples.

Re: Query FeatureLayer by date

JakeSkinner — Tue, 29 Apr 2014 16:01:53 GMT

I believe so. Querying a REST service worked for either method:

Date = date '05/16/2014'

or:

Date = date '2014-05-16'

Re: Query FeatureLayer by date

JimWharton — Sun, 12 Dec 2021 02:52:26 GMT

Ok, perhaps I'm misunderstanding.

I've attempted to prepend 'date' to my queries. They're still misunderstood.

"time < date'"+this.esriDate(endDate)+"'"

Still throws an error.

I haven't found any docs telling me what date format ESRI expects.

Re: Query FeatureLayer by date

JakeSkinner — Tue, 29 Apr 2014 16:16:09 GMT

Where is the data coming from, i.e. SQL Server, File Geodatabase, etc? The following link may be helpful:

http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000

Re: Query FeatureLayer by date

JimWharton — Tue, 29 Apr 2014 17:11:26 GMT

My data is just coming from a FeatureLayer.

I'm not actually directly querying a database. When using a query (https://developers.arcgis.com/javascript/jsapi/query-amd.html) I just need to return features newer than a particular date that is set by the user.

Re: Query FeatureLayer by date

JohnGrayson — Wed, 30 Apr 2014 14:45:10 GMT

Jim,
If possible, please check the values in this field at the REST service endpoint to verify that the field values are returned by the service as an integer value that represents the number of milliseconds since midnight Jan 1, 1970 in UTC. Looking at the json response of the query is what I would suggest.

So, if you can create a valid Date instance from the string returned by the HTML5 widget (that truly represents the date and time you want to query with) then I would suggest you use the ".valueOf()" method on the Date instance to get the value in UTC milliseconds and use it in your query. For example:

query.push( "time > " + (new Date(startDate)).valueOf() );

More info on Date objects: JavaScript Date Object

If this does not work, then it would help if we had a public map service endpoint that your query is trying to access so we can figure out how to properly do this date query.

Re: Query FeatureLayer by date

JimWharton — Sun, 12 Dec 2021 02:52:29 GMT

Ok, doing that (turning it into a standard Unix timestamp, gives me this as a query param: where:time > 1398729600000

That looks correct, but I'm still getting the same error.

The entire method looks like this:

        buildQuery: function() {
          var query = [];
          var name = this.username.value;
          var startDate = this.startDate.value;
          var endDate = this.endDate.value;

          if(name) {
            query.push("username LIKE '%"+name+"%'");
          }

          if(startDate) {
            query.push("time > "+new Date(startDate).valueOf());
          }

          if(endDate) {
            query.push("time < "+new Date(endDate).valueOf());
          }

          if(query.length > 1) {
            return query.join(' AND ');
          } else {
            return query.join('');
          }
        },

The feature layer definition has list of fields is as follows:

Fields:
FID (type: esriFieldTypeInteger, alias: FID, SQL Type: sqlTypeInteger, nullable: false, editable: false)
username (type: esriFieldTypeString, alias: username, SQL Type: sqlTypeNVarchar, length: 254, nullable: true, editable: true)
lat (type: esriFieldTypeDouble, alias: lat, SQL Type: sqlTypeFloat, nullable: true, editable: true)
lng (type: esriFieldTypeDouble, alias: lng, SQL Type: sqlTypeFloat, nullable: true, editable: true)
accuracy (type: esriFieldTypeDouble, alias: accuracy, SQL Type: sqlTypeFloat, nullable: true, editable: true)
heading (type: esriFieldTypeDouble, alias: heading, SQL Type: sqlTypeFloat, nullable: true, editable: true)
time (type: esriFieldTypeDate, alias: time, SQL Type: sqlTypeTimestamp2, length: 8, nullable: true, editable: true)
photo (type: esriFieldTypeString, alias: photo, SQL Type: sqlTypeNVarchar, length: 254, nullable: true, editable: true)
photothumb (type: esriFieldTypeString, alias: photothumb, SQL Type: sqlTypeNVarchar, length: 254, nullable: true, editable: true)