# AQL , DATE(TIME) and WHERE **Category:** [AQL](https://discourse.openehr.org/c/aql/43) **Created:** 2020-08-31 10:57 UTC **Views:** 1663 **Replies:** 34 **URL:** https://discourse.openehr.org/t/aql-date-time-and-where/955 --- ## Post #1 by @bna 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//value > "2020-31-08T12:04:10"` Is this equivalent with: `WHERE o//value > "2020-31-08T12:04:10.0Z"` --- ## Post #2 by @thomas.beale My original thinking was always that all date/times should be written as the date/time of the locale, WITH the timezone appended (i.e. `+0100` for most of Europe, and `Z` for UK, Portugal). On the assumption that most use of the data is likely to be where it was created, this makes life easy - just strip off the TZ part. But if you move the data to another timezone it's also easy to understand, and more useful in it's locale-based form (since the time of day might be important in the sense of social time, i.e. morning, night-time etc). I don't think we mandated this in the specs though. I'm not sure what current systems are doing. But I'd propose adopting the above rule if at all possible. --- ## Post #3 by @bna Our implementation (the backend) will assume UTC if no TimeZone is defined. Which means the client have to make sure it is right. If i.e. the client lets the user enter date as a string -it's the clients responsibility to add the TZ information for the given client. The backend can't, IMHO, magically know the TZ of the client. One solution might be to add metadata to the AQL request where the client provides such information. Or the client will convert the given (input) datetime to UTC before calling the backend. --- ## Post #4 by @sebastian.iancu 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 the full format (as it is mentioned also in REST)... --- ## Post #5 by @yampeku I think every engine assumes the machine's locale if no timezone is provided --- ## Post #6 by @bna Even though the production use is in Norway we've had issues with this. Happens if some servers is set up with wrong TZ information. The proposed solution makes the propabililty for such situations lower. It's always more safe to be specific about the TZ. But AFAIK the specification is not distinct on that. --- ## Post #7 by @bna [quote="yampeku, post:5, topic:955, full:true"] I think every engine assumes the machine’s locale if no timezone is provided [/quote] Which machine? The client or the server? --- ## Post #8 by @yampeku server, the one that receives the query or has to store the result --- ## Post #9 by @pablo In general, my opinion is a datetime without TZ is an incomplete datetime. I agree on using the server/database TZ as default is correct, though, I configure our servers (application + database) to be always in UTC (+00:00), so both are correct: 1. take server TZ as default, 2. use UTC as default. What we always do is when sending data to the client from the server, we add the TZ, so the client side can translate to the local time. This approach assures everything works OK on any environment without messy configuration or specific logic on the backend. --- ## Post #10 by @sebastian.iancu In my comments above I was was assuming that the problem is with a client-server environment - the query is made by client, which might be on a different TZ than the server itself. The same problem is with times set/generated by clients (e.g. in forms) - there is not sufficient data for the server to safely "detect" and persist the TZ when such values are partial. Of course, server may easily set the TZ for the server-side events and data, and that could be the local TZ (if that's the design). --- ## Post #11 by @ian.mcnicoll I can see arguments for both approaches. 1. Be completely explicit about the data i.e full datetime+ tz in query parameters 2. Allow local defaults for TZ. Maybe what we are missing is some kind of means of documenting these kind of defaults publicly as part of the 'system' object that we are starting to consider - essentially at CDR level. This going to get more important as the scope of the CDR and apps grows. --- ## Post #12 by @bna Our goal is portability. This require shared rules for this IMHO. It's not precise enough to let the server choose its local TZ. I think my suggestion is the best so far 😋 --- ## Post #13 by @ian.mcnicoll Of course your suggestion is better .. but not all of us have your dedication to truth and justice. :wink: Some of us just want an easy life ... --- ## Post #15 by @bna [quote="ian.mcnicoll, post:13, topic:955"] Some of us just want an easy life … [/quote] That will be my next life ☀️🙏 This life was spent trying to get timing correct.. The question is in fact larger than above. Because it depends on the intention by the user. Do you want a global or local temporal event. We've found that the CDR cannot distinguish such ideas in the head of the user without being explicit told. Thus we have to make a decision on a strategy. Our decision is to always use UTC if other not stated. Some can always build tooling to make the life of Ian simpler. I assume most clients will do this. But that's another story and not the responsibility of the backend. --- ## Post #16 by @ian.mcnicoll I do agree, I'm just conscious that not everyone will be in a position to make that jump, and a compromise might be for the CDR to explicitly publish any default assumptions about dateTimes without a TZ in queries. --- ## Post #17 by @thomas.beale 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. --- ## Post #18 by @matijap 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 should handle the time zone conversion appropriately, i.e. not "conveniently ignore" the TZ part, nor store the raw data with times altered due to TZ conversion. 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. --- ## Post #19 by @thomas.beale [quote="matijap, post:18, topic:955"] I believe the system should handle the time zone conversion appropriately, i.e. not “conveniently ignore” the TZ part [/quote] 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. If the data were sent somewhere else, then the TZ may need to be taken into account, but even then it is not clear - a specific date/time might be intended to represent something like '9:30 am on 6 Aug 2019', wherever the patient happened to be - Bombay, London or Boston. Also, if the TZ approach were adopted, your (date/)times would be the same as they were, but with the TZ (`+0100`) appended, i.e. the logic of generating the main value would be unchanged. I'm not saying anyone should do this, just pointing out that it could work without blowing up current systems, as long as you allowed older times to have no TZ part. --- ## Post #20 by @bna [quote="matijap, post:18, topic:955"] 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. [/quote] We do the same AFAIK. Just to make sure we understand the topic: My question is only related to AQL and how to interpret the given example with this fragment of an AQL: **A:** `WHERE o//value > "2020-31-08T12:04:10"` **B:** `WHERE o//value > "2020-31-08T12:04:10.0Z"` And the question is simple: Should the openEHR CDR evaluate this as **A** is equivalent with **B** For us, DIPS EhrStore, this holds true. And I argue for this to be the most consistent way of dealing with data from possible different timezones and that is why we've implemented it this way. Another, and possible, solution is to evaluate **A** as any datetime on the 31th of August 2020 at 12.04:10 in any possible timezone. This will certainly give a wider resultset than the previous one. --- ## Post #21 by @matijap [quote="bna, post:20, topic:955"] My question is only related to AQL and how to interpret the given example with this fragment of an AQL [/quote] First paragraph of my previous answer relates to just that (i.e. timestamp values in AQL queries); I wasn't clear enough. --- ## Post #22 by @erik.sundvall 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 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). I have also said like @sebastian.iancu and others that it is the responsibility of a client to supply timezone suffix in clinical content when storing data. On the other hand I believe it is reasonable to use UTC (our backend platform standard), with the Z suffix, in the AUDIT_DETAILS.time timestamp that I believe will always be set by the CDR server, not the client. **Does the above with "clinical content" vs AUDIT_DETAIL timezone usage sound reasonable or just confusing?** --- P.S. I or my colleagues will get likely get back with more questions regarding e.g.: * configurability of default server timezone behaviours for missing mandatory timestamps (e.g. int POSTusing simplified formats) * default AQL query/execution timezone behaviours with and without timezone suffixes in the AQL WHERE parameter (it looks like some implementations just do partial string handling rather than implementing real time-zone-independent comparable time, e.g. @joshua.grisham knows more) --- ## Post #23 by @erik.sundvall @joshua.grisham just supplied more details in the thread: https://discourse.openehr.org/t/should-compositions-be-written-with-local-timezone-or-utc-datetimes/4440 So we can continue with relevant details there instead of here. --- ## Post #24 by @siljelb [quote="erik.sundvall, post:22, topic:955"] 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). [/quote] From a clinical application building perspective, agree wholeheartedly. Clinically relevant datetimes in UTC would lead to a lot of confusion. I don't care as much about the datetimes above the level of `context/start_time`. --- ## Post #25 by @Seref 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. --- ## Post #26 by @ian.mcnicoll 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 the mixed approach described should work. As long as the software rules are all described clearly, enforced and understood both approaches should work. We did have recent issue with an integration where one part of the incoming data had been exported as 'normalised to UTC' by replacing the local timezone with 'Z'!! Chaos!! --- ## Post #27 by @ian.mcnicoll [quote="Seref, post:25, topic:955"] we end up normalising to UTC alongside the local date/time [/quote] Do you mean you carry both dates in the database? --- ## Post #28 by @bna As I remember we do the same thing. Datetimes are normalized to UTC to share a common variant for analytics and queries. This will make most use cases work out of the box. Some cases like query for events happening at ie 10 local time you need to make some query plans. --- ## Post #29 by @erik.sundvall @bna, in the most common DIPS use cases where I assume you control all the UIs, I guess converting from UTC to local time every time is easier to enforce than in a more diverse ecosystem with many developers/applications from different suppliers. **Where** does DIPS usually make the converson - in App UI code, reused UI widget code, middleware, CDR server or elsewhere? @pablo did I understand your previos post correctly that you, like DIPS, also store everything as UTC in the CDR, but convert it to local time on the fly (serverside?) before sending it to clients - so that client application code only see local time? And a repeating a previous question to all, but especially @thomas.beale who concieved and wrote things like section 4.6 in https://specifications.openehr.org/releases/RM/latest/ehr.html#_time_in_the_ehr : [quote="erik.sundvall, post:22, topic:955"] Does the above with (local time for) “clinical content” vs (UTC for) AUDIT_DETAIL timezone usage sound reasonable or just confusing? [/quote] --- ## Post #30 by @thomas.beale Normally (= in the past) times in audits for commits, reads etc of contents of a DB would be in the local time of the 'location' of the DB, but of course today, that actually means the 'location of the tenant owner'. Even then it might be that with a) data being served from anywhere, b) mergers & acquisitions among hospital systems and so on it is no longer safe not to use either UTC or else TZs everywhere. Personally the TZ approach seems more logical, since it provides an indication of the location of the original creation of the data, whereas UTS doesn't. --- ## Post #31 by @pablo Note: some databases don't support timezones to be stored in native datetime types. Some do support TZ via extensions and some fully support TZs in datetime types. With that being said, the DBMS used might lead to the technical need of storing all in UTC or have a separated field/column, with the corresponding size penalty, to store the TZ. In any case, if there is a TZ, implicit or explicit, in the datetime, then it can be converted to any other TZ, so data query, retrieval, analysis and visualization shouldn't be an issue when the TZ is known or it has a sensible default. Of course, conversions are always needed. This is a long discussion in informatics in general. My conclusion is that there is no best solution, it's arbitrary, and any solution can work as long as the TZ is known. --- ## Post #32 by @bna [quote="erik.sundvall, post:29, topic:955"] @bna, in the most common DIPS use cases where I assume you control all the UIs, I guess converting from UTC to local time every time is easier to enforce than in a more diverse ecosystem with many developers/applications from different suppliers. **Where** does DIPS usually make the converson - in App UI code, reused UI widget code, middleware, CDR server or elsewhere? [/quote] Datetime is one of the hardest thing to get right. We don't have timezones in Norway. Or more precisely we have timezones two times a year when changing from winter to summer time and back. *Summertime* in Norway is UTC+2, while *Wintertime* is UTC+1. Healthcare professional working the night, when the clock is changed, need to be able to see chronological events for that night. At some of our customers infrastructure we also had a situation with issues related to how the servers where configured when it come to TZ. This lead to some situations where data suddenly got a few hours wrong. This where soon identified and fixed. Based on these situations we had to make the openEHR stack of components "self-contained" and robust when it comes to datetime handling. We save the datetimes using the timezone and index the data in the CDR using UTC time. Reading data the client will then be able to sort/order the cronological sequence of events. So to answer your question: The conversion is made far behind in the server and the clients need to be TZ aware when displaying data. Disclaimer: It's a few years since I worked with the technical details about datetime handling - which I found challenging to get my head around. If you need a more in-detail and precise description on how we handle this I need to consult one of the system architects who actually implemented this. --- ## Post #33 by @erik.sundvall [quote="bna, post:32, topic:955"] We **save the datetimes using the timezone** and index the data in the CDR using UTC time. Reading data the client will then be able to sort/order the cronological sequence of events. So to answer your question: The conversion is made far behind in the server and the clients need to be TZ aware when displaying data. Disclaimer: It’s a few years since I worked ... If you need a more in-detail and precise description on how we handle this I need to consult one of the system architects who actually implemented this. [/quote] @bna so if we look from an openEHR REST API perspective at the DIPS CDR, do I understand correctly that all the timestamps in the data going in and out via that API are in local time including timezone info (suffixed w/ +01:00 or + 02:00 in your case) and that the UTC-index is used mostly internally (inside the CDR) for things like efficiently sorting the returned data in e.g. AQL-queries that request time sorting? Yes, please ask your system architect if needed, I think this discussion could be helpful to many. Same question to @Pablo what time format (UTC or local time with TZ info) is seen via the openEHR REST API in your openEHR products? --- ## Post #34 by @pablo [quote="erik.sundvall, post:33, topic:955"] Same question to @Pablo what time format (UTC or local time with TZ info) is seen via the openEHR REST API in your openEHR products? [/quote] IIRC in Atomik if you do a GET /composition the compo has the original time expressions, and when you a data query, the datetime data comes all in UTC since datetime is indexed transformed to UTC. The idea is to avoid results that are on different TZs (getting a time series and the client needs to standardize that every time is painful). --- ## Post #35 by @erik.sundvall Great that the /composition maintains the original time expressions, I was a bit worried it didn't. --- ## Post #36 by @pablo Also, it's the easiest implementation since it doesn't require to do TZ transformations on incoming data. Though when data is indexed for querying, it's standardized, at least in our implementation. --- **Canonical:** https://discourse.openehr.org/t/aql-date-time-and-where/955 **Original content:** https://discourse.openehr.org/t/aql-date-time-and-where/955