Spotfire Ideas Portal
In the MS world, developers are frequently using the DATETIMEOFFSET type to specify a point in time at a given time zone offset. The type is common in SQL Server and C# code (probably more). Data like this ends up in SQL Server data sources.
TDV does not handle this type natively, but instead provides it as a VARCHAR(34) field.
There are several issues with that:
It is not possible to apply date/time functions to such a field (for example to aggregate on month), but it has to be converted to a compatible type first,
There is no fully compatible type. While TIMESTAMP is very similar in nature, if converting a DATETIMEOFFSET VARCHAR(34) value to a TIMESTAMP, the time zone offset is lost. This is a problem when converting DATETIMEOFFSETs that are in the interval of DST fall-off, where the time zone offset of the original field changes, while the TIMESTAMP part repeats the same hour.
For example, in time zone 'Europe/Amsterdam', DATETIMEOFFSETs '2022-10-30 02:15:00.0000000 +02:00' and '2022-10-30 02:15:00.0000000 +01:00' both get converted to the same TIMESTAMP '2022-10-30 02:15:00'.
Because the value is treated as a character field, it suddenly matters whether the TDV server is configured case-sensitive or not trimming trailing white-space, even though that is not actually applicable to the native type.
Filtering on the value works well on the unconverted field, but when converted to a TIMESTAMP using a CAST or similar function, filtering performes badly due to converting all rows and then filtering. If the conversion were to be native to TDV, the server could apply inverse conversions internally and push the query to the data source.
We worked around this issue by creating a pair of custom Java functions[1] for the conversions in both directions. They take a pair of parameters: a DATETIMEOFFSET or a TIMESTAMP respectively, and a time zone name to convert it to. This works well for most of the bullet points, but is still not ideal:
You still need to keep in mind whether you are filtering on the value or whether it is being displayed and convert accordingly (for a filter it is better to inverse-convert a constant that the field is compared to, instead of the field value itself, for example)
Applying the function is manual. It is easy to forget on a new view.
Providing a target time zone (name) is required.
Every customer who has to deal with this SQL Server type needs to implement something similar as we did.
An automatic conversion would be ideal, but how to convert to the correct time zone is an open question.
I know that PostgreSQL, for example, always stores timestamps at UTC and uses the client time zone to convert it to a local timestamp (they also have 2 types of timestamps: timestamp with time zone and timestamp without time zone, that handles whether this conversion should be done or not).
For our scenarios, getting TIMESTAMPs at UTC out of our DATETIMEOFFSETs would be fine. To handle the other way around, though, it would probably be necessary that the data-source settings (SQL Server) would include the desired time zone.
I think that the mssql capabilities file should have multiple possible conversions for DATETIMEOFFSET fields:
to UTC or
to local time, with the possibility to override the local time zone specified in the OS somewhere.
Additionally, a TIMESTAMP converted from a DATETIMEOFFSET and a time zone should track that time zone, such that an additional AT TIME ZONE statement is based on the correct zone. Currently (with our implementation), when an AT TIME ZONE 'UTC' is applied to a DATETIMEOFFSET field converted to a local zone TIMESTAMP, no conversion is taking place as the server assumes the field to already be at zone UTC. (Yes, there is TZCONVERTOR, but that function doesn't get pushed to the data source, which is rather inefficient in cases where we filter on such fields).
Ad 1: I can see if I am allowed to share these functions, if people are interested. Java has a very similar native type and understands time zone names, so the conversions are not very hard to implement with a bit of Java know-how, so it's not a lot of work to duplicate the idea once the problem is well-understood.