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

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.

Find more posts tagged with

ArcGIS Online

Accepted answers

All comments

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

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

@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

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'}
)

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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