AQL with WHERE , and when you “filter” data on some DATE(TIME) without given a specific timezone. Should the AQL engine (the CDR) assume it to be UTC timezone? I.e. WHERE o/<path>/value > "2020-31-08T12:04:10" Is this equivalent with: WHERE o/<path>/value > "2020-31-08T12:04:10.0Z"

In our case, if TZ not supplied we assume the locale TZ (easy an safe, as we only operate in NL). But I think from specifications point of view it is much safer to assume Z (UTC). As Bjorn is saying, it is not easy/safe for the server to guess the TZ of the client. Best practice is anyway to send t…

Another advantage of having times as local values with TZ appended in the DB is that most of the time, processing of queries containing a time can be done just by matching from the start of the stored date/time strings, and the TZ part will be conveniently ignored.

Our system assumes server’s local time zone. I’m not advocating that as the best solution, but explicitly specifying something else at this point would be a breaking change for us… which we could work around with yet another configuration switch, of course. As for the querying, I believe the system…

[image] matijap: I believe the system should handle the time zone conversion appropriately, i.e. not “conveniently ignore” the TZ part What I meant by that is for the case where the system knows the query is going to execute on data created in the same locale, and thus the TZ doesn’t matter. …

[image] matijap: We store things exactly as they are posted (if they are posted in RAW format, of course), and also return them that way, but the query engine understands how to compare date-times in different TZs. We do the same AFAIK. Just to make sure we understand the topic: My question …

[image] bna: My question is only related to AQL and how to interpret the given example with this fragment of an AQL First paragraph of my previous answer relates to just that (i.e. timestamp values in AQL queries); I wasn’t clear enough.

At Karolinska we are also stumbling upon these issues now, so reviving this thread… We will want to run our entire backend platform (CDR + PMI + FHIR services and a LOT of other things) on UTC to make e.g. event/log correlation and many other things easier. I have (as @thomas.beale above) argued f…

[image] erik.sundvall: I have (as @thomas.beale above) argued for that the time inside the main clinical data e.g. COMPOSITION.context.start_time / end_time and timing in entires should be in local time, including timezone info stored (including daylight saving changes etc). From a clinical a…

That’s how we do it (how being local time with tz). Our view is that clinicians should always get the format that has the least cognitive load. For various purposes, especially for analytics, we end up normalising to UTC alongside the local date/time.

I completely agree that users should always visualise dates with local timezones inc time-saving, however my understanding is that it is now straightforward to translate between local TZ and UTC, even for historical dates. My preference would be to standardise the data and the querying on UTC, but…

AQL , DATE(TIME) and WHERE

Specifications AQL

erik.sundvall (Erik Sundvall) 12 September 2023 10:48 23

@joshua.grisham just supplied more details in the thread: Should Compositions be written with local timezone or UTC datetimes? So we can continue with relevant details there instead of here.

Topic		Replies	Views
Should Compositions be written with local timezone or UTC datetimes? Specifications rest-apis , aql	10	667	20 September 2023
Datetimes in AQL and API AQL	16	1301	15 March 2021
AQL through GraphQL Platform rest-apis , aql	21	2411	14 February 2022
CDR implementation of partial DV_TEMPORALs Implementation	9	111	29 April 2026
Conformance data validation question: counterintuitive C_TIME.range constraints Specifications time	7	504	22 May 2022
[EHRbase] Storing and querying data without an standalone archetype Platform aql , ehrbase	39	1008	17 April 2024
AQL: Expected result with repeating structures Implementers (archive)	9	16	6 May 2017
Constrain timezone in date/time fields Clinical	8	483	28 December 2022
Timing in the real world, openEHR and FHIR Clinical fhir , time	23	1483	27 May 2020
Business Intelligence tools with openEHR support Integration	21	2360	11 September 2024

AQL , DATE(TIME) and WHERE

Related topics