I now present the next installation of the blog series “Querying Feature Services”. Feature services and map services provide an operation on layer resources for querying through the REST API. This query operation provides many options which allows clients to build powerful applications. This article will focus on date-time queries specified using the query operations where parameter. See query feature service layer for more information.

It’s important to know that standardized queries are enabled on services by default, allowing you to provide a standard SQL 92 style syntax across all data sources. For a list of supported SQL functions in ArcGIS Server, visit here. You are able to turn off standardized queries to make data source specific requests using a larger set of SQL, but you should keep in mind that doing this limits security checks, and you will be more vulnerable to SQL injection attacks. If you want to turn it off, see this page.

Note: When you turn off standardized queries on the server it does not apply to ArcGIS Enterprise hosted services, these will continue to support standardized SQL.

Date Functions

You can query esriFieldTypeDate (date-time) fields in two different ways: by DATE or TIMESTAMP date functions. Each type of date-time query must include a date function to make sure the query is treated in the proper way.

<DateField> = DATE 'YYYY-MM-DD'
<DateField> = TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

When should you use each type of date-time query? Well that’s a good question, and the answer is that it depends on your data and what you want from it. Let’s say my feature service has a date field called Sighting that contains the date and time of bird sightings in the United States. I want to know how many birds were spotted on June 5th, 2018 and I don’t care what time of day they were seen. In this case, I’ll use the DATE function and format my query like this: Sighting = DATE '2018-06-05'.

Now let’s say I want to know if there were any bird sightings on June 5th, 2018 at 5:30 pm. Using date and time together requires the TIMESTAMP function, and will look something like this: Sighting = TIMESTAMP '2018-06-05 17:30:00'. It’s important to note that the query needs to use 24-hour time values.

For the examples above I used the = operator, but any of the following comparison operators can be used in the where clause: >=, <=, >, <, <>. For example, I want to query all the birds spotted on or after June 1st, 2018, so I will format my query using greater than or equal to: Sightings >= DATE '2018-06-01'. You might be asking, “Wait, what if I want to query my data and find all the birds spotted between two dates?” Well you’re in luck! I introduce to you the BETWEEN logical operator (bet you didn’t see that one coming). You can use this and other logical operators with any date query format, for example: Sightings BETWEEN DATE '2018-06-01' AND DATE '2018-06-05'.

Wow! Who else is blown away by date-time queries? The above queries are extremely powerful when working with date-time fields, but what if I told you that they can get better?

Standard Date Queries

<DateField> >= CURRENT_TIMESTAMP - DD

This format is not supported across all types of feature services, so if you’ve formatted the query properly according to the syntax above and are getting an error message, this type of query is not supported on your feature service. For a generic syntax, see the newly added (as of 10.6.1) INTERVAL syntax in the section below. This format is used by specifying the day, or fractions of days. What do I mean by that? I mean that the DD value in the query can be a decimal value representation of the number of days you want to query. Continuing from the example above, let’s say that you want all bird sightings in the United States within the last three days. If today is June 4th, 2018, 3 days ago would be June 1st, 2018 so your query would look like this: Sightings >= DATE '2018-06-01'. But if you execute the same query the next day, now all of a sudden, the query returns all of the bird sightings in the last 4 days. What if you need an automated query to see all the bird sightings for the past three days? You will have to manually adjust the date in the query each time you want that answer, and who wants to do that? Not me, that’s for sure. That’s where this syntax really shines!

To solve this problem, the query would look like this: Sightings >= CURRENT_TIMESTAMP - 3. This query returns all the sightings in the last three days consistently, no matter when it is executed. This is particularly useful for things like filtering crowd-source data in a way that gives users the most recent information without manual intervention. To give more examples of how you can use this, let’s say you want all the bird sightings within the last 6 hours, the query would look like this: Sightings >= CURRENT_TIMESTAMP - 0.25. On the other side of that, if you wanted all the bird sightings in the last 2 and a half days, the query would look like this: Sightings >= CURRENT_TIMESTAMP - 2.5.

Interval Queries

Starting at 10.6.1, you can make use of INTERVAL queries. When can you use them? Well, the INTERVAL syntax can be used in place of the date-time queries above, and will be standardized across all map and feature services. Currently, this syntax is supported with ArcGIS Enterprise hosted feature services (except for services using a spatiotemporal big data store) and feature services published referencing a registered enterprise database. Work is being done to support this syntax in ArcGIS Online, and should be available in the next release.

Now, although the queries have a pretty simple format, there are several specific ways to use the INTERVAL syntax (don’t worry, it’s all very similar to the query format above). INTERVAL queries have a specific syntax for all supported feature services.

For use with INTERVAL there are two main formats for date and timestamp:

<DateField> >= CURRENT_DATE -+ INTERVAL '<IntervalValue>' <TimeStampFormat>

<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL '<IntervalValue>' <TimeStampFormat>

The most commonly used format is CURRENT_TIMESTAMP, as it is universal for both date and time queries. To solve the problem mentioned above with INTERVAL, you can format the query like this: Sightings >= CURRENT_TIMESTAMP - INTERVAL '3' DAY.

You aren’t limited to just DAY when using INTERVAL either. Here are some of the other formats you can use:

<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD' DAY
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH' HOUR
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'MI' MINUTE
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'SS(.FFF)' SECOND

<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH' DAY TO HOUR
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH:MI' DAY TO MINUTE
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'DD HH:MI:SS(.FFF)' DAY TO SECOND
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH:MI' HOUR TO MINUTE
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'HH:MI:SS(.FFF)' HOUR TO SECOND
<DateField> >= CURRENT_TIMESTAMP -+ INTERVAL 'MI:SS(.FFF)' MINUTE TO SECOND

Note: Except for the second (.FFF) value (which can be a decimal value), all values must be integers.

These query formats can be used with CURRENT_DATE in place of CURRENT_TIMESTAMP, and can be used with + or - INTERVAL values.

Let’s take the previous query one step further and say that I want all bird sightings in the last 3 days, 5 hours, 32 minutes, and 28 seconds. In this case, the query will look like this: Sightings >= CURRENT_TIMESTAMP - INTERVAL '3 05:32:28' DAY TO SECOND.

Notes

• If a date field is specified in the outFields list, the date-time will always be returned in formatted UTC.
• For both relational and spatiotemporal hosted feature services, CURRENT_TIMESTAMP will always pass and return the date and time in UTC. Values are also always stored in UTC. For referenced services, CURRENT_TIMESTAMP is passed directly to the back end database and the database will use the time zone of the machine that it is running on, which can be the same time zone as dateFieldsTimeReference.