Filter Data by Time of Day

[ZachBodenner]

Hello,

I have a large dataset of public safety incidents that I'm building a dashboard for. The Dash contains a list of incidents that I have set up a Date Selector for to assist the Fire Captain with filtering by date. However, they would also like to be able to filter by time of day only (so the result of the filter would show, for example, all historic incidents that occurred between 9 and 10 PM). Is this possible with any tool, widget, selector, etc within Dashboards? 

(Currently Enterprise 10.8.1)

Thanks!

[jcarlson]

It's sort of possible. If you created a Data Expression for your dashboard, you could insert an ad-hoc "time of day" field based on the date field. But I don't think those are available at 10.8.1, so the only alternative would be to create a new field / fields for any time-related information, then figure out a way to auto-calculate these fields at regular intervals.

In any case, there will be some coding in Arcade or Python involved. Do you have access to the schema to add fields?

Also, how are incident reports coming in? Do you have access to the incoming data, where you could have the fields you want populating before being added to the layer?

[ZachBodenner]

The data is coming in as a SQL view on a variety of tables. So I did try to create essentially an identical view and add a new column to get the time following some of the suggestions on this thread:

https://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql

I tried both a CAST and a CONVERT. I tried a cast on the date/time field and the result was another full datetime, except the date and year were the same for every table row though it did preserve the time. The CONVERT did work, and then the last thing I tried was the suggestion to trim off the time from the field but predictably that just output to a text field (I should note here that I'm still learning a lot about SQL and perhaps there are some other ways to accomplish this I just don't know about). 

You're right, the Arcade scripting isn't available in 10.8.1 - though fingers crossed I'll be upgrading us to 11.1 later this year. 

Do you have any other thoughts on the source table?

[jcarlson · Accepted solution]

Not sure what RDBMS you're on, but try extracting the hours and minutes from the datetime. Usually something like:

• HOUR(datetime)
• DATEPART(HOUR, datetime)
• EXTRACT (HOUR FROM datetime)

One of those ought to work. If you want the time to be more precise, do the same thing for minutes, then add in minutes / 60. That'll give you a numeric value between 0 and 24, corresponding to the hour of the day the incident occurred.

[ZachBodenner]

Alrighty so what I did was to structure my view to use DATEPART to pull out the hour, which gets returned as an integer. So then in the dashboard I added a number selector, which introduces a small quirk of having to filter "0" for anything that occurs in the midnight hour, but that's a small thing. Thanks for the suggestion!