Representing more complex date/time information in the schema

[timgdavies]

In https://github.com/openreferral/api-specification/issues/23 there is a discussion of being able to sync data about service opening times from the 'events' section of a services website, into referral datasets.

At present, HSDS supports schedules, which give opening times week on week.

Calendar tools, and event plugins on websites, generally support events and recurring events.

So synchronising this into HSDS would either require:

• Adding the concept of events to HSDS;
• Finding a reasonable way to map exception-laden patterns of events and recurring events into a reasonable schedule representation;

Thoughts on this issue for future iterations of HSDS would be welcome.

[timgdavies]

Related to #175

[klambacher]

We have an event element that is separate from the operating hours. It includes information about recurrence conditions (e.g. every X months,, every X weeks, XYZ days of week, 4th Tuesday of the month, blah blah blah), optional start and end dates and times, and so on. Multiple event "schedules" are permitted for a single organization/program. If you would like details about our data structure, please let me know and I'll point you to it - understanding that it's relatively new for us and I'm sure there's room for improvement.

[StateSupport]

We (MN legal services) have also added information to capture events. We added a new table, event schedules, with the following fields: id, service_id, weekday, week_of_month, start_date, end_date, start_time, end_time, recurrence (none, weekly, monthly), and separation count.

It looks like our approach is similar to @klambacher's approach above. We are just starting out in our Open Referral implementation and are open to feedback and happy to share more once our documentation is finalized.

[digitalWestie]

A resource we could draw inspiration from is RRule: https://jakubroztocil.github.io/rrule/

[mathewtrivett]

RRULE is also an internet calendaring standard developed by the IETF (Internet Engineering Task Force). If implemented properly this could make "recurring activities" compatible with all sorts of calendar type applications. However it does add some overhead to data management.

[odscjames]

I would also look at RRule. They have already thought of all the reoccurrence cases and there are tons of libraries for taking an RRule and spitting a list of dates out already.

However I will sound a note about one problem I've seen in the past when trying to crowd source events; people will happily set up an RRULE and then not come back and do anything about exceptions.

For instance, like "Last Monday of the month". Maybe one year the last Monday of the month happens to be Christmas day? So the event organisers will just say "oh, we'll have Dec off" and cancel but they won't update their data listing ..... so your then promoting bad data!

I know keeping data up to date is an issue you all already think about, I just wanted to be clear about a particular issue that will come up with reoccurring events.

[MikeThacker1]

In our work in England (see here in GitHub), our analyst has proposed extending regular_schedule to add fields which allow events to be defined according to the iCal standard - see entity relation diagram.

This does create one compliance problem with the current OpenReferral standard:

regular_schedule.weekday is required by OpenReferral but not compatible with the extension to use iCal

[greggish]

@mikethacker1 what does your analyst think about RRULE (linked above)?

[robredpath]

Update regarding spring 2020 upgrade cycle: an interesting development since the early days of OR is that APIs are now much more common as a design pattern, while HSDS is principally designed as a bulk interchange format (although it does then inform the response format of HDSA). This, then, informs what we should think about when considering how to represent date and time information in the schema.

In the UK, I've heard conversation about how far we need to support the desire for up-to-date information, that takes into account both predictable and unpredictable circumstances, and how far it's ok to support just "advertised opening hours". For example, there's flooding right now in some areas of the UK. In the former case, I might want to use a service to find out if the playgroup that my kids attend is open, but in the latter case I'd know that the app that I used to find the service doesn't provide real-time information. HSDS supports the latter use case far better, but we might want to either formalise that further (if we think that's what we want), or we might want to look at how we can support more real-time information.

By contrast, OpenActive does attempt to offer real-time information, and so I'd expect to see if my session was cancelled via an app that used OA. A thought that occurred to me recently was that we might be able to derive HSDS from OA for service directory applications. But, I digress.

RRULE is very expressive and supports (I think...) every use case I've heard about, but is quite hard to work with. In a database, you can't carry out simple arithmetic to work out if a service is available at a certain time (for example), and if we start to require system builders to parse the data in order to make it easy to use, then we're starting to chip away at one of the core value propositions of a standard - that the modelling is done for you.

We might take design inspiration from RRULE and add fields for all the various parameters that RRULE allows. This might appear daunting at first, but we could address that with good docs.

Or, we might want to limit the expressiveness of HSDS, and simply encourage 'advanced' users to model complex information in a way that works for them.

Open to discussion!

[Bjwebb]

@MikeThacker1 Do you have some examples of what API queries on your subset of ical fields looks like? For example, is it possible to construct a query to list everything that's open next Saturday?

[MikeThacker1]

https://openreferraluk.org/Guidance/ shows the proposed extension to regular_schedule.

To check something is open next Saturday, I would expect "SA" to be included in the "byday" field and next Saturday to come between the "valid_from" and "valid_to" dates.

I think our current API specification to GET services makes provision for the day and time but currently does not use the valid from and to dates. It would be sensible for us to add those as optional extra filters.

[Bjwebb]

To check something is open next Saturday, I would expect "SA" to be included in the "byday" field and next Saturday to come between the "valid_from" and "valid_to" dates.

This excludes events that are monthly and use bymonthday.

This incorrectly includes events that use byday, but are not every week.

[Rovack]

Just chiming in to say this would be an extremely valuable feature for us as well: At Streetlives we've found this to be probably the most noticeable issue with the schema since we started using HSDS. And using RRULE sounds like a great solution!

However, I'd also like to echo the concerns raised about being able to query RRULE data, particularly for checking whether a service is open at a given point in time.

Our HSDS-based app allows users to filter service providers on a map by various parameters, and the most basic one is "Open now". I haven't worked with RRULE before, but from this discussion it's not clear to me whether that could be done with a reasonable DB query in a manner that covers all RRULE cases. (Naturally, for performance reasons it's important to be able to run such filtering logic in the DB and not BE code.)

I don't know if this sort of thing usually falls within the scope of HSDS, but do you suppose it might be possible to have some proposed implementation of such querying logic, to go along with the schema? I think that would be incredibly helpful, and help make this feature so much more usable.

[Maxastuart]

I, too, am on the RRule train.

I would also look at RRule. They have already thought of all the reoccurrence cases and there are tons of libraries for taking an RRule and spitting a list of dates out already.

However I will sound a note about one problem I've seen in the past when trying to crowd source events; people will happily set up an RRULE and then not come back and do anything about exceptions.

For instance, like "Last Monday of the month". Maybe one year the last Monday of the month happens to be Christmas day? So the event organisers will just say "oh, we'll have Dec off" and cancel but they won't update their data listing ..... so your then promoting bad data!

I know keeping data up to date is an issue you all already think about, I just wanted to be clear about a particular issue that will come up with reoccurring events.

I think this issue comes up even without passing recurring events. It's simply an issue inherent in real-life schedule upkeep.

RRule's exception list actually seems like a pretty decent way to manage them, as I can imagine frontend's that allow organizations to set their service opening hours as a user-friendly schedule:

(WIP Draft!!)

...then use RRule to render the proposed schedule on a calendar-view which would have options to modify the individual events (i.e. holiday hours) and add those changes to the initial RRules as exceptions (RRuleSet.prototype.exdate(dt))

I suggest adding both RRule string format (the input read by RRule.fromString) and RRule instances themselves (a potentially infinite list of Datetime strings or limited by some standard, i.e. 2yrs from today, stored on an additional table) to HSDS in lieu of the REGULAR_SCHEDULE table's *WEEKDAY and OPENS_AT columns. The CLOSES_AT column would be changed to LENGTH_OF_SLOT to be calculated on render on frontends.

While this would require a different REGULAR_SCHEDULE entry for each timeslot a Service is open for a different length of time (which is no different than the current schema), it would eliminate the need for the HOLIDAY_SCHEDULE table because the exceptions to events would be contained in the RRule strings and lists of Dates. One-time events with unique repetion intervals or lengths would simply require additional REGULAR_SCHEDULE entries.

It might even be possible to store them all as RRule strings if it is as complete as it appears for exception handling and as quick as one hopes for rendering on the frontend for display/editing or backend for searching (though on the projects I work on we use separate denormalized DBs indexed on multiple dimensions to facilitate searching/filtering by important facets like "open now" or "open next" and "proximity nearby", which is what I would render the RRule instance-lists-of-datetimes+lengths on)

Hi all! I may be new here but I've been working in this space for 2 years, @AtticusRains prompted me to chime in here as it's relevant to the longstanding project I've been involved in, the SF Service Guide (HSDS DB), as well as my current time-sensitive ones for HungerFreeAmerica and SouperBowl of Caring collecting and mapping all food-service locations offering assistance to people in need in the US (for the latter, as part of the TackleHunger Map project we'll be sending weekly reminders to charities to update their statuses and providing an easy interface to update schedules).

I hope my additions provide some useful food-for-thought :)

[devinbalkind]

Hi folks. Does anyone have any sample data showing how scheduling works in HSDS 2.0? The reference fields aren't making sense to me, specifically where start/end times go, but if I can see a working example I think I'll get it. ;)

http://docs.openreferral.org/en/latest/hsds/reference/#schedule

Thanks.

[MikeThacker1]

Some of the results in this query https://tools.openreferraluk.org/ApiQuery/?endpoint=https://bristol.openplace.directory/o/ServiceDirectoryService/v2&execute=true&keywords=vision%20west in our API Query Tool show the new schedule fields in what we still call regular_schedule in the last version of Open Referral UK (extending Open Referral before the HSDS 2.0 changes).

So "Bristol Pub Social - Vision West of England" looks like this:

[image: image.png]

I don't think any of the schedules is particularly complicated and I have not checked them, so don't know if they'll answer your questions.

Mike

*Mike Thacker* Porism Tel / Signal / WhatsApp: +44 7976 295 784 Bon Marché Centre, 241–251 Ferndale Road, London, SW9 8BJ, England mike.thacker@porism.com | porism.com http://twitter.com/MikeThacker http://plus.google.com/+MikeThacker

On Tue, 27 Oct 2020 at 17:51, Devin Balkind notifications@github.com wrote:

Hi folks. Does anyone have any sample data showing how scheduling works in HSDS 2.0? The reference fields aren't making sense to me, specifically where start/end times go, but if I can see a working example I think I'll get it. ;)

http://docs.openreferral.org/en/latest/hsds/reference/#schedule

Thanks.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/openreferral/specification/issues/167#issuecomment-717416275, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACSVFHPRZGGUOLDI727SOFLSM4CBLANCNFSM4DXZGEDQ .

See our eCasework http://e-sd.org/36vJM/ for casework management designed especially for councillors

[MikeThacker1]

And this link gives a service with an example in JSON format: https://api.porism.com/ServiceDirectoryService/services/ddd88a14-1bd5-41d9-a3ff-dad8976c9136

Mike

*Mike Thacker* Porism Tel / Signal / WhatsApp: +44 7976 295 784 Bon Marché Centre, 241–251 Ferndale Road, London, SW9 8BJ, England mike.thacker@porism.com | porism.com http://twitter.com/MikeThacker http://plus.google.com/+MikeThacker

On Wed, 28 Oct 2020 at 10:29, Mike Thacker mike.thacker@porism.com wrote:

Some of the results in this query https://tools.openreferraluk.org/ApiQuery/?endpoint=https://bristol.openplace.directory/o/ServiceDirectoryService/v2&execute=true&keywords=vision%20west in our API Query Tool show the new schedule fields in what we still call regular_schedule in the last version of Open Referral UK (extending Open Referral before the HSDS 2.0 changes).

So "Bristol Pub Social - Vision West of England" looks like this:

[image: image.png]

I don't think any of the schedules is particularly complicated and I have not checked them, so don't know if they'll answer your questions.

Mike

*Mike Thacker* Porism Tel / Signal / WhatsApp: +44 7976 295 784 Bon Marché Centre, 241–251 Ferndale Road, London, SW9 8BJ, England mike.thacker@porism.com | porism.com http://twitter.com/MikeThacker http://plus.google.com/+MikeThacker

On Tue, 27 Oct 2020 at 17:51, Devin Balkind notifications@github.com wrote:

Hi folks. Does anyone have any sample data showing how scheduling works in HSDS 2.0? The reference fields aren't making sense to me, specifically where start/end times go, but if I can see a working example I think I'll get it. ;)

http://docs.openreferral.org/en/latest/hsds/reference/#schedule

Thanks.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/openreferral/specification/issues/167#issuecomment-717416275, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACSVFHPRZGGUOLDI727SOFLSM4CBLANCNFSM4DXZGEDQ .

See our eCasework http://e-sd.org/36vJM/ for casework management designed especially for councillors

[devinbalkind]

I think opens_at and closes_at are missing from this reference table - aren't they?

http://docs.openreferral.org/en/latest/hsds/reference/#schedule

[MikeThacker1]

Seems opens_at and closes_at are not in RRULE (see https://jakubroztocil.github.io/rrule/ ).

In the UK it looks like we kept these fields from the old spec along with iCal/RRULE fields.

Mike

*Mike Thacker* Porism Tel / Signal / WhatsApp: +44 7976 295 784 Bon Marché Centre, 241–251 Ferndale Road, London, SW9 8BJ, England mike.thacker@porism.com | porism.com http://twitter.com/MikeThacker http://plus.google.com/+MikeThacker

On Wed, 28 Oct 2020 at 14:54, Devin Balkind notifications@github.com wrote:

I think opens_at and closes_at are missing from this reference table - aren't they?

http://docs.openreferral.org/en/latest/hsds/reference/#schedule

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/openreferral/specification/issues/167#issuecomment-717987827, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACSVFHKQLGI6PLILORJFF4LSNAWBTANCNFSM4DXZGEDQ .

See our eCasework http://e-sd.org/36vJM/ for casework management designed especially for councillors

[devinbalkind]

But Mike -- then how do I document when things open and close!?

[robredpath]

@devinbalkind @MikeThacker1 @greggish Good catch - we did accidentally delete those fields!

Re-added in https://github.com/openreferral/specification/pull/229

[devinbalkind]

Awesome. Thanks.

There are two fields in RRULE that look potentially useful to me that maybe we should include?

• tzid - allows us to define a timezone
• count - defines how many occurrences before a rule expires

Seems to me we as might as well include as much of RRULE as could be practically useful, but maybe not?

[devinbalkind]

Another question: if I want to overwrite something - can I do that with the existing specification?

For example: this service is open 9-5 monday-friday except for these dates/holidays when it closed, or when it is open 12-3, etc.

If there were a field to identify that a row was special/holiday and should be imposed above the others then I think this issue would be resolved.

Thoughts? And thanks!

[mrshll1001]

I am closing this since RRULE is implemented in the current version (3.0, and looks like it was adopted before that?) @devinbalkind I think some of the guidance one exceptional schedules may answer your question? Please re-open this issue if not.