Missing support for DateTimeOffset with offset in LINQ queries?

[Torgeir-Hansen]

Hi,

When I try to query with LINQ using a DateTimeOffset with a timezone, it throws an exception.
If I use UTC time it works as expected.

It seems I can read and store documents with a timezone offset just fine, I'm hoping this is just a minor tweak that needs to be done to the linq provider? 🤔

Code to reproduce (LastUpdated on Person is a DateTimeOffset):

IQuerySession session = _database.AsQueryable().Session!;
DateTimeOffset queryTime = DateTimeOffset.Now.AddDays(-1);
IReadOnlyList<Person> queryResult = await session.Query<Person>()
   .Where(p => p.LastUpdated < queryTime).ToListAsync(token: cancellationToken);

Exception message:

Cannot write DateTimeOffset with Offset=01:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported. (Parameter 'value')

Stack trace:

at Npgsql.Internal.Converters.DateTimeOffsetConverter.WriteCore(PgWriter writer, DateTimeOffset value)
at Npgsql.Internal.PgBufferedConverter1.Write(PgWriter writer, T value) at Npgsql.Internal.PgBufferedConverter1.WriteAsObject(Boolean async, PgWriter writer, Object value, CancellationToken cancellationToken)
at Npgsql.Internal.PgConverter.WriteAsObjectAsync(PgWriter writer, Object value, CancellationToken cancellationToken)
at Npgsql.NpgsqlParameter.WriteValue(Boolean async, PgWriter writer, CancellationToken cancellationToken)
at Npgsql.NpgsqlParameter.Write(Boolean async, PgWriter writer, CancellationToken cancellationToken)
at Npgsql.Internal.NpgsqlConnector.WriteBind(List1 parameters, String portal, Byte[] asciiName, Boolean allResultTypesAreUnknown, Boolean[] unknownResultTypeList, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.<Write>g__WriteExecute|101_0(NpgsqlConnector connector, Boolean async, Boolean flush, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken) at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token) at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token) at Polly.ResiliencePipeline.<>c__92.<b__9_0>d.MoveNext()
--- End of stack trace from previous location ---
at Polly.Outcome1.GetResultOrRethrow() at Polly.ResiliencePipeline.ExecuteAsync[TResult,TState](Func3 callback, TState state, CancellationToken cancellationToken)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler1 handler, CancellationToken token) at JasperFx.Core.Exceptions.ExceptionTransformExtensions.TransformAndThrow(IEnumerable1 transforms, Exception ex)
at JasperFx.Core.Exceptions.ExceptionTransforms.TransformAndThrow(Exception ex)
at Marten.Exceptions.MartenExceptionTransformer.WrapAndThrow(Exception exception)
at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler1 handler, CancellationToken token) at Marten.Linq.MartenLinqQueryable1.ToListAsync[TResult](CancellationToken token)

[jeremydmiller]

It's a limitation in more recent versions of Npgsql. And the "fix" is probably going to be just having Marten quietly convert the time to UTC for you anyway. This is going to be an "I take pull requests" issue most likely if you're interested.

[jeremydmiller]

@Torgeir-Hansen Meh, I'm trying to do a bug sweep and release today. I'll take a shot at this, but all it's going to do is make a ToUniversal() conversion as it builds the NpgsqlCommand.

[gfoidl]

And the "fix" is probably going to be just having Marten quietly convert the time to UTC for you anyway.

Technically it's that easy.

But I prefer in not doing so, rather move the conversion to users's code and leave the exception here.

Why?
In user code with offset != 0 then I'm able to store the data, Marten converts it to UTC and stores it.
Upon retrieval of data I may assume an automatic conversion too, but how should Marten know the correct offset for converting back? If Marten doesn't convert back to local timezone, no data will be returned.
So that situation isn't ideal. Thus I'm pushing towards that user code should handle the conversion (and the error message is helpful in that regards too).

[nkosi23]

Consider using NodaTime data types since they are supported by both Npgsql and Marten.

I implemented a basic appointment booking system a couple of months ago with Marten where timezone handling was of the essence. I'll have to get back to you tomorrow with the details as i am not 100% sure of whether or not i queried against dates, but i remember that nodatime played a role (never heard of it before that).

Maybe i stored as Utc and just stored the offset using nodatime. Will get back you on that, middle of the night here.

[nkosi23]

Well, just checked and I essentially store the IANATimeZone and UTC. I use the NodaTime utility functions/types to help with conversion and display (DateTimeZoneProviders.Tzdb.GetZoneOrNull(ianaTimeZone) etc... I think I settled on this approach because web browsers are able to return and use standard IANATimeZones.

Well in my case I actually store the DateTimeOffset for other reasons (see NodaTime's ZonedDateTime.FromDateTimeOffset(dateTimeZone)) but I never query against it.

I agree that it's probably better to let Marten blow up instead of doing silent conversions that would cause folks to lose the timezone information without realizing it. I don't think it is a bug, people should simply clarify their design. Even at a conceptual level, querying against a DateTimeOffset is a confusing concept (one may argue that the database should compare by converting to utc, but in many cases folks may actually have a different domain-specific comparison logic in mind). I think it's actually helpful to throw this exception to invite people to revisit their design early in the development process.

[jeremydmiller]

The NodaTime LINQ support is a nightmare too, no free lunch here.