Datetimes in AQL and API

Specifications AQL
1

We were discussing in HiGHmed / EHRBASE about the accepted formats for dates, times and specially datetimes in AQL.

The spec [Archetype Query Language (AQL)] says those datatypes should comply with ISO 8601 which allows to:

a. use dots or commas for the fraction seconds marker
b. avoid the time separator if “mutually agreed”, that is the ‘T’ in aaaammddThhmmssZ

Currently EHRBASE doesn’t support using commas for the fraction seconds marker or not having the ‘T’ time separator, so AQLs with those formats will fail.

I suggested to add in the EHRBASE documentation what is supported and what is not. @birger.haarbrandt mentioned that should be something better agreed at the SEC level because adding constraints just to one implementation could difficult migrating queries from one implementation o another.

I agree in looking for “mostly compatible” queries, we need to address this at the SEC. Another point is, the current spec by saying “this is ISO 8601” it is actually allowing commas in the fraction seconds marker and use or not the time separator, which is OK from the specification point of view.

It would be interesting to know what others think and get some input on what other implementations support or not in the datetime formats.

There is one extra case for the query API, when the datetime value is in the JSON as a parameter instead of hardcoded in the AQL, please consider:

Hardcoded datetime in the AQL expression:

{
“q”: “SELECT c/uid/value FROM EHR e[ehr_id/value=$ehr_id] CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.review.v1] WHERE c/context/start_time/value = ‘20200607195544.339Z’”,
“query_parameters”: {
“ehr_id”: “{{ ehr_id }}”
}
}

Datetime as a parameter in the JSON:

{
“q”: “SELECT c/uid/value FROM EHR e[ehr_id/value=$ehr_id] CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.review.v1] WHERE c/context/start_time/value = $dt”,
“query_parameters”: {
“ehr_id”: “{{ ehr_id }}”,
“dt”: ‘20200607195544.339Z’
}
}

While the format for the hardcoded AQL value should comply with the AQL rules, the dt parameter should comply with the “JSON rules” if the type is datetime, but it could be also a string so no specific format is needed.

Reading about datetimes in JSON, there are no specific rules, but some recommend to use the JS rules, which generates this format “2020-06-08T17:04:30.621Z” (ISO 8601 extended with dot as fraction seconds marker and the time separator), which comes from executing new Date().toJSON() in JS.

My question about this is: do we consider the $dt value to be a datetime or a string? and should we enforce any specific formatting when included in the JSON instead of in the AQL expression?
​

2

Not specifically AQL, but we have APIs we provide to partners and in those APIs we have data points for which we committed to supporting ISO8601. They can get really creative at times and I end up with an exception in the logs (happened today actually). Then I fix it. In summary, if we say ISO8601, then we do ISO8601. I think it is fine to say we’re not fully supporting this at the moment and fix missing subsets of ISO8601 later. It feels more cumbersome to agree to not to support some part of it in SEC, what if a partner already implemented it fully :slight_smile: ? Maybe I misunderstood your suggestion re SEC, in that case, sorry, it’s been a long day.

Probably an entirely different discussion, but this looks like you’re using a session based parameter replacement approach. I’d suggest leaving this approach behind and moving to stateless calls.
Assuming you’re happy with this a.t.m, I’d suggest sticking to valid ISO8601 strings in json, i.e. plan json string. fhir does the same JSON is just a serialisation format here, and it feels wrong to me to lean towards a particular language’s (JS) encoding when moving tech. agnostic data around.

My 2 pennies of course. Happy to hear what others think

3

For me is OK to say “our implementation supports what the spec supports” and the spec saying “we support ISO8601” which means all the possible variants.

In that case, we need to fix our implementation, but we can still say “these are known issues and for now we don’t support some creative alternatives”. I liked what you said about getting the exceptions and keep expanding to more alternatives in the backend.

Also, I think we need to return friendly errors, so the API client knows exactly what happened like “this date doesn’t follow what we currently support” and they can fix accordingly (because not all clients might know what ISO 8601 is and send any kind of data on the datetime data points).

Thinking out loud, this might also have to do with a suggestion I did to @thomas.beale a while ago about adding type checking to the AQL expressions (if a path points to a datetime node, the value it is compared to should be a valid/parsable datetime).

4

The general approach in standardisation is to limit what is in the standard initially (less choices, but less difficulty conforming) and grow it over time, in a controlled way. So we could revise the AQL spec to state only the ‘extended form’ of ISO 8601 strings, i.e. with the ‘-’ and ‘:’, ‘T’ etc.

But we’d really need to know what the current implems support to know if that is sensible to do at this point.

The type-checking could be done by using Archie and the BMMs, which we have for the RMs.

5

Yes, we need input from implementations.

For EHRBASE I can confirm both extended (yyyy-mm-ddThh:mm:ssZ) and basic (yyyymmddThhmmssZ) formats are supported, but only the dot seconds fraction marker is allowed. With support to the timezone being ‘Z’ or full ‘-0300’ (I didn’t test if it supports the extended format for the timezone ‘-03:00’)

6

I think this is a good suggestion, but lets start/continue another discussion topic to continue discussing this, or a JIRA PR/CR for ITS-REST.

7

I would support a tighter definition around the extended form as you have suggested, and alignment with FHIR as far as possible will make lives much easier.

I thought we had already done something about this in terms of data?
https://specifications.openehr.org/releases/RM/latest/data_types.html#_representation

8

I remember discussions we had about 2-3 years ago around datetime.
If I not mistaken, the conclusion was each implementation should internally support ISO format (including partial, with or without TZ or with microseconds), but of course each implementation has its own preferred or default format. About data instances, the question was if implementation should be able to deal with ‘imported’ content from other implementation by converting external format to internal format - the conclusion was that data should be kept as it is (as long as format is correct).
On the other hand services such the REST API, are the one should communicate in standard format, so that we ease interoperability, and there we choose the extended JS-format (which is one variant of ISO, and also aligned with FHIR, and generally rest-api friendly).

1 Like
9

Yes good link.
Also, about rest, see “datetime format” under https://specifications.openehr.org/releases/ITS-REST/Release-1.0.1/#design-considerations-data-representation

1 Like
10

Good one, there “Query parameters and path segments that are date, datetime, or time, MUST always use the extended ISO8601 format, e.g. 2016-06-23T13:42:16.117+02:00”

I think that allows also to use comma as a seconds fraction marker and optional T time delimiter.

Sadly I don’t have access to the ISO standard, just to Wikipedia https://en.wikipedia.org/wiki/ISO_8601

" A decimal mark, either a comma or a dot (without any preference as stated in resolution 10 of the 22nd General Conference CGPM in 2003,[25] but with a preference for a comma according to ISO 8601:2004)[26] is used as a separator between the time element and its fraction."

" A single point in time can be represented by concatenating a complete date expression, the letter “T” as a delimiter, and a valid time expression. For example, “2007-04-05T14:30”. It is permitted to omit the “T” character by mutual agreement as in “200704051430”.[31] Separating date and time parts with other characters such as space is not allowed in ISO 8601, but allowed in its profile RFC 3339.[32]"

Should we add more constraints to the API description? e.g. by saying only “.” is accepted and “T” is required

11

I’m inclined to do that, but again, we need all the implem builders to tell us if that works for them. It may be that we need to create a separate document for an ‘openEHR ISO 8601 profile’ to hold all these constraints / deviations in one place. Such a document could also describe more than one level of profile, e.g. a more advanced one would allow comma or dot, and also non-extended format (say).

1 Like
12

Not sure if all implementers have access to this discussion, can we send them a personal query about this topic? I think that will be more efficient.

13

I did some quick tests on Better EHR Server and it seems like we support all of the formats (basic and extended, with period and with a comma, with Z or full zone offset, with or without a colon in the zone offset) but the missing ‘T’ – we fail if T is missing in either basic or extended format of date-time.

2 Likes
14

This was the agreement we had 2-3 years ago in SEC, hence that line in REST spec. Perhaps not clear or normative enough stated in REST specs? Then we can detail it for the upcoming release - i created a ticket for now: [SPECITS-45] - openEHR JIRA

15

Is there a record for that? I don’t recall the conversation.

The current REST spec text is:

"Datetime format

Query parameters and path segments that are date, datetime, or time, MUST always use the extended ISO8601 format, e.g. 2016-06-23T13:42:16.117+02:00.

Timezone SHOULD be only supplied when needed and that when not supplied, the local timezone is assumed.

Any date, datetime or time value provided by a create or update action inside the COMPOSITION content will be preserved as it was sent by client (i.e. if COMPOSITION was saved as narrow format, it will always return the way it was). However, the strongly RECOMMENDED format is to use the ISO8601 extended format. This is to enable time information in EHRs shared across timezones to be computed properly."

I guess is not enough in the sense it doesn’t mention what it’s on my previous message (specifics about fraction marker and time delimiter).

Also the “narrow” format I think it’s formally called “basic” format. Again, that is from the wikipedia page (ISO 8601 - Wikipedia). If someone has access to the ISO spec, we can consult there.

16

Hi @bna would it be possible to check the time formats allowed in AQL on the DIPS implementation? I think we need your input to move forward on this, thanks!

17

It’s been a while, but there is now progress on this issue.

On REST we have now SPECITS-45 fixed, check the text on Overview - Datetime format.

On AQL this issue was also addressed, see section 3.12.5. Dates and Times.