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?


WHERE o/<path>/value > "2020-31-08T12:04:10"

Is this equivalent with:
WHERE o/<path>/value > "2020-31-08T12:04:10.0Z"

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.

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.

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)…

I think every engine assumes the machine’s locale if no timezone is provided

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.

Which machine? The client or the server?

server, the one that receives the query or has to store the result

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.

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).

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.

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 :yum:

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 …

1 Like

That will be my next life :sunny::pray:

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.

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.

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 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.

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.

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/<path>/value > "2020-31-08T12:04:10"

B: WHERE o/<path>/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.

First paragraph of my previous answer relates to just that (i.e. timestamp values in AQL queries); I wasn’t clear enough.