Cosmos: DateTimeOffset with non-zero offsets are not supported by functions

[roji]

The Cosmos provider allows inserting DateTimeOffsets with arbitrary offsets, but the Cosmos functions don't appear to support them:

SELECT DateTimePart('hh', '2020-01-01 12:30:15') -- 12
SELECT DateTimePart('hh', '2020-01-01 12:30:15Z') -- 12
SELECT DateTimePart('hh', '2020-01-01 12:30:15+02:00') -- undefined

Unfortunately, even for zero offsets, we generate e.g. 0010-05-03T12:45:38+00:00, which isn't supported; we need to generate 0010-05-03T12:45:38Z instead. We could consider not allowing DateTimeOffsets with non-zero offsets.

[ChrisJollyAU]

What would the behavior be if you have an offset? Convert to UTC and save that?

[roji]

I'm not sure... PostgreSQL doesn't have a DateTimeOffset type either (with an offset), so the EF provider allows saving DateTimeOffsets only if their offset is zero, and throws for others. Implementing that for Cosmos would be a possibly problematic breaking change, though...

[ChrisJollyAU]

I'll make note that the docs https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/working-with-dates recommend saving date and time strings is to use UTC in ISO 8601 format.

I went along that sort of thinking for EFCore.Jet and just converted them all to UTC DateTime to save.

In fact even the GetCurrentDateTime only operates in UTC.

Something to consider. Can see how it would be a breaking change though

[roji]

Sure, converting to UTC is another option - though that's an option which doesn't roundtrip. There's something to be said for basically asking the user to do that on their side, so they're aware of exactly what's going on and what's gonna be saved.