FHIR Chat · DateTime processing in JDBCQueryBuilder · ibm

Stream: ibm

Topic: DateTime processing in JDBCQueryBuilder

Lee Surprenant (Oct 24 2019 at 22:17):

Paul did some work to get our tests running in Java 11, but I have some local edits to JDBCWholeSystemSeardchTest that I believe to be confirming that we have an issue with our datetime processing

Lee Surprenant (Oct 24 2019 at 22:39):

I opened https://github.com/IBM/FHIR/issues/305 for this issue, but we can discuss it here

Paul Bastide (Oct 24 2019 at 23:33):

I have many of the same questions as you

Lee Surprenant (Oct 25 2019 at 00:10):

I opened https://github.com/IBM/FHIR/pull/306/files with what seems to be the fix. Not totally sure why it was failing in Java 11 and not Java 8, but we basically just need to avoid using Date when processing the search parameter value.

Lee Surprenant (Oct 25 2019 at 00:14):

Open question: what should be the behavior when an instant/dateTime has
some number of ms digits and the search has less precision?
This PR introduces tests for this scenario too, but I've commented them out for now. Should we handle it as part of #305 or open some issue for the future?

Paul Bastide (Oct 25 2019 at 00:15):

Good question... it's a hard one need to think about the various scenarios

Lee Surprenant (Oct 25 2019 at 00:15):

i think my second commit to https://github.com/IBM/FHIR/pull/306 outlines the scenarios pretty well: https://github.com/IBM/FHIR/pull/306/commits/64ab697f7dd7cf034628a5dfc7048b5314151851

Lee Surprenant (Oct 25 2019 at 00:16):

the commented out tests are the ones that are failing now

Paul Bastide (Oct 25 2019 at 00:17):

<check/>

Lee Surprenant (Oct 25 2019 at 00:18):

in my opinion, those should all be passing. to make it happen, i think we'd need to handle almost every search as an implicit range. so _lastUpdated=2019-01-01T12:00:01Z should become WHERE x >= 2019-01-01 12:00:01 AND x < 2019-01-01 12:00:02

Lee Surprenant (Oct 25 2019 at 00:18):

we'd only use "exact match" semantics for search parameter values with a full 6 digit ms field

Paul Bastide (Oct 25 2019 at 00:19):

right we'll have to do some dance to build the range

Lee Surprenant (Oct 25 2019 at 00:19):

we already do similar for computing the end date of implicit ranges for Year, Month, and Day precision

Lee Surprenant (Oct 25 2019 at 00:19):

https://www.hl7.org/fhir/search.html#date is pretty clear with those

Lee Surprenant (Oct 25 2019 at 00:20):

its just odd that it doesn't even mention fractional seconds

Lee Surprenant (Oct 25 2019 at 00:20):

in fact, if you strictly adhered to the description in this section, you'd think fractional seconds aren't even allowed

Lee Surprenant (Oct 25 2019 at 00:21):

The date parameter format is yyyy-mm-ddThh:mm:ss[Z|(+|-)hh:mm] (the standard XML format).

Lee Surprenant (Oct 25 2019 at 00:23):

but then the very next paragraph says

Any degree of precision can be provided

Lee Surprenant (Oct 25 2019 at 00:40):

on a related note, i think we actually lose precision of these fractional seconds in the model:

Basic basic = Basic.builder()
    .code(CodeableConcept.builder().text(string("test")).build())
    .extension(Extension.builder()
        .url("http://example.com")
        .value(DateTime.of("0001-01-01T01:01:01.100000Z"))
        .build())
    .build();
System.out.println(basic);

{
    "resourceType": "Basic",
    "extension": [
        {
            "url": "http://example.com",
            "valueDateTime": "0001-01-01T01:01:01.1Z"
        }
    ],
    "code": {
        "text": "test"
    }
}

Lee Surprenant (Oct 25 2019 at 00:40):

i.e. no way to determine the number of digits provided from a parsed dateTime

Paul Bastide (Oct 25 2019 at 00:40):

interesting, probably not preferred

Lee Surprenant (Oct 25 2019 at 00:41):

i doubt it matters much for 99% of use cases

Paul Bastide (Oct 25 2019 at 00:41):

I think John gave me some code to do so using parseBest

Lee Surprenant (Oct 25 2019 at 00:41):

but still interesting

Lee Surprenant (Oct 25 2019 at 00:44):

it just means we can't tell the difference between a search for 0001-01-01T01:01:01.1Z (which i'd like to handle as x >= 0001-01-01T01:01:01.1Z and x < 0001-01-01T01:01:01.2Z) vs 0001-01-01T01:01:01.100000Z (which should only match if x is exactly 0001-01-01T01:01:01.100000Z)

Lee Surprenant (Oct 25 2019 at 14:12):

I spoke with @John Timm on this one and here's what I'm thinking we should do:

1. continue storing fractional seconds in db
2. convert hh:mm:ss query param value into range queries on the db (e.g. [01:01:01,01:01:02))
3. continue to allow searches to come in with fractional seconds, and treat those as "exact matches" with infinite precision (01:01:01.1 would not find 01:01:01.100001)

Lee Surprenant (Oct 25 2019 at 19:52):

@John Timm @Paul Bastide I just pushed an update that implements this proposal: https://github.com/IBM/FHIR/pull/307
I also updated the Conformance.md to document the behavior. Please review when you have a minute.

Paul Bastide (Oct 25 2019 at 19:54):

LGTM

Lee Surprenant (Oct 25 2019 at 20:17):

merged

Last updated: Apr 12 2022 at 19:14 UTC