nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query FeatureLayer by date

JimWharton

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.

Vind meer berichten getagd met

Accepted answers

All comments

JakeSkinner

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 + "'";

JimWharton

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];

JimWharton

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.

JakeSkinner

Try the following:

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

JimWharton

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?

JakeSkinner

Try this:

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

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

JimWharton

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.

JakeSkinner

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

Date = date '05/16/2014'

or:

Date = date '2014-05-16'

JimWharton

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.

JakeSkinner

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

JimWharton

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.

JohnGrayson

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.

JimWharton

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)

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.