How do you get a date query to work?

10049
16
10-09-2014 07:40 AM
AndrewWolff1
New Contributor II

I have a date field in my hosted feature service that is being collected through the collector app.  I'm trying to build a query in the Web AppBuilder that selects records from a specific day however the query fails when the input is a date..  If I query date is blank or not blank it works fine.   Has anyone been able to create successful query based on date?

16 Replies
RobertScheitlin__GISP
MVP Emeritus

Andrew,

   Here is what a working date query looks like for me in the json file:

Begin_Date_Occ BETWEEN timestamp '2014-10-15 00:00:00' AND timestamp '2014-10-15 23:59:59'

0 Kudos
AndrewWolff1
New Contributor II

I was hoping someone could tell me how to configure the GUI box within the app builder.

0 Kudos
RobertScheitlin__GISP
MVP Emeritus

Andrew,

   In Beta 3 all you do is choose your date field, then operator "is between" and then set the beginning and ending date selector fields. I just tested this against a layer in my map that has a date field and it worked great.

0 Kudos
AndrewWolff1
New Contributor II

Continued musings on this:

Trying to query not using a between operator and using an "is on" fails.  So does the Is on "date" operator fail because the data is being collected with time?  Must you always use a between statement?

Also is there anyway to query date is equal to today's date.(getdate)  so that the map is dynamic for each day its opened?

I find this works better with operations dashboard having an "is within" operator when you select a relative date but still not the best.

Unfortunately I don't have any options for modifying or hosting my own application, I can only work withing the out of the box AGOL environment at this time.

0 Kudos
TroyProctor
New Contributor III

I'm also having an issue using the Query Tasks Widget for the Web AppBuilder in ArcGIS online.

We've collected data via the Collector tool and that data is hosted as as feature service on AGOL. When trying to search by the CreationDate (Date) field with any of the operators I get a query failed message.

Thanks,

Troy Proctor

0 Kudos
JacquelineWilson
New Contributor

I know how to fix this, but I'm not sure it's a good idea. The problem is that the query is created like so:

myDate > timestamp '2014-11-28 23:59:59'

*or*

myDate BETWEEN timestamp '2014-11-28 00:00:00' AND timestamp '2014-11-30 23:59:59'

The file webappviewer/jimu.js/main.js constructs the query and inserts "timestamp" via the following condition:

(this.isHosted?"":"timestamp ")

... which likely messes up those of us hosting, because these queries work just fine:

myDate BETWEEN '2014-11-28 00:00:00' AND '2014-11-30 23:59:59'

myDate BETWEEN '2014-11-28' AND '2014-11-30'

myDate > '2014-11-28'

etc

You can test this with your REST url query tool.

Screen Shot 2014-11-29 at 9.33.17 AM.png

I modified the main.js file and removed all instances of timestamp for all date operators, and it all works. However, I recognize the intent of the query structure to avoid hassles with timezones etc. and I'm not sure why it causes issues. Anyone have a better way?

Jacqueline Wilson

SubuSwaminathan1
Occasional Contributor

Jacqueline,

Thanks for the tip. This issue continues to exist in a WAB 1.1 when talking to a ArcGIS Server 10.2.2 endpoint.

I was able to fix it by removing      + (this.isHosted ? "" : "timestamp ")   from the filterUtils.js file in the jims.js folder of your app.

Subu

timbrown3
New Contributor

I know this is an old post, but I am experiencing the same issue. However I am a noob with web mapping applications, but tying to learn. I am able to download the web map application to my desktop as a zip file from arconline and modify the code as you suggest, but I am not seeing how to re upload it except to host it, is that what you are doing or is there a way to upload the web mapping application to ArcGIS online, or at least the widget.

thx,

tim

0 Kudos
RobertScheitlin__GISP
MVP Emeritus

Tim,

   Once a code change is made then there is not way to upload the app back to AGOL. You would have to host the app on your own web server.