An indicator that displays records made during working hours

[jarvesuu]

I have records in a data table where one column contains the date and time of entry.I wish to display an indicator in the ArcGIS dashboard that would show the entries made during working hours. No matter how much I struggle, I can't seem to display this result.None of the filter options are suitable.

[jcarlson]

I think you might need a Data Expression. Unfortunately, datetime fields are always going to have a pesky date aspect, so 8:15 on Monday will look earlier than 7:15 on Tuesday.

You could easily display the in/out of business hours for a single item in a List or Table, but to aggregate the information, you'll need the time to behave like an actual field.

You could iterate over your features and calculate that for each, but fortunately, we can do this with GroupBy. This allows us to send a SQL expression to the feature service, where it will evaluate much faster, and it returns a FeatureSet, so we don't have the hassle of building our own.

Here's an example, using the hours 8:00 and 17:00 as my "business hours" limits.

// get your feature service
var fs = FeatureSetByPortalItem(
  Portal('https://www.arcgis.com'),
  'itemid of your service',
  0, // layer id
  ['objectid'],
  false
);

// sql expression to see if a value is between 08:00 and 17:00
var sql = `CASE
WHEN EXTRACT(HOUR FROM created_date) > 8 AND EXTRACT(HOUR FROM created_date) < 17 THEN 'YES'
ELSE 'NO'
END`

return GroupBy(
  fs,
  {name: 'in_bus_hours', expression: sql},
  {name: 'the_count', expression: '1', statistic: 'COUNT'}
)

[KenBuja]

@jcarlson  I was examining a way of using the SQL Extract in a Filter statement, but was running into an issue where the EXTRACT(HOUR FROM field) was returning values that were 4 hours off. Did you see that at all?

return Filter(fs, "EXTRACT(HOUR FROM created_date) BETWEEN 8 and 17")

 

[jcarlson]

It depends on the feature service. AGOL knows to apply a timezone offset between the DB and the client, but this method would be running against the stored value, possibly in UTC.

[KenBuja]

I was wondering if time zone had something to do with it, but the points were collected in the Eastern time zone and I'm viewing them in the Eastern time zone. The UTC differential should be five hours.

[jcarlson]

Daylight Savings?

[KenBuja]

They were collected in the summer. I was wondering if you saw the same thing when running your script with your data.

[jcarlson]

No, but I was testing against a layer that is in Central Standard at the DB level, not UTC. I do see time zone offsets on some other expressions where the underlying layer is storing UTC values, though.