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)…
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.
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).
Be completely explicit about the data i.e full datetime+ tz in query parameters
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.
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.