Convert DateTimeOffset to DateTime UTC to prevent npgsql exception storing DateTimeOffset as
TimestampTz with npgsql 6.*:
InvalidCastException: Cannot write DateTimeOffset with Offset=02:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
[^1]: UTC DateTime is written as timestamp with time zone, Local/Unspecified DateTimes are written as timestamp without time zone. In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DateTime is always written as timestamp without time zone.
The npgsql <6.0.0 behavior was to silently discard the offset component but in npgsql 6.0.0, it throws an InvalidCastException.
System.InvalidCastException: Cannot write DateTimeOffset with Offset=02:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTimeOffset value, NpgsqlParameter parameter)
at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
at Npgsql.NpgsqlParameter.ValidateAndGetLength()
at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Community.Microsoft.Extensions.Caching.PostgreSql.DatabaseOperations.SetCacheItemAsync(String key, Byte[] value, DistributedCacheEntryOptions options, CancellationToken cancellationToken)
at Microsoft.Extensions.Caching.Distributed.GroupClaimsDistributedExtension.SetGroupClaimsAsync(IDistributedCache cache, ClaimsPrincipal principal, IEnumerable`1 groupNames, DistributedCacheEntryOptions options) in /Users/breiter/src/wolfereiter/cuny-nypd-video/lib/wolfereiter-graph-claimstransform/src/WolfeReiter.Identity.Claims/Extension/GroupClaimsDistributedCacheExtension.cs:line 19
at WolfeReiter.Identity.Claims.AzureGroupsClaimsTransform.TransformAsync(ClaimsPrincipal principal) in /Users/breiter/src/wolfereiter/cuny-nypd-video/lib/wolfereiter-graph-claimstransform/src/WolfeReiter.Identity.Claims/AzureGroupsClaimsTransform.cs:line 77
at Microsoft.AspNetCore.Authentication.AuthenticationService.AuthenticateAsync(HttpContext context, String scheme)
at Microsoft.AspNetCore.Authentication.RemoteAuthenticationHandler`1.HandleAuthenticateAsync()
at Microsoft.AspNetCore.Authentication.AuthenticationHandler`1.AuthenticateAsync()
at Microsoft.AspNetCore.Authentication.AuthenticationService.AuthenticateAsync(HttpContext context, String scheme)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Convert DateTimeOffset to DateTime UTC to prevent npgsql exception storing DateTimeOffset as TimestampTz with npgsql 6.*:
The correct behavior is to store a UTC DateTime or a DateTimeOffset with Offset=0 as TimestampTz .
(table reproduced from npgsql.org)
[^1]: UTC DateTime is written as timestamp with time zone, Local/Unspecified DateTimes are written as timestamp without time zone. In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DateTime is always written as timestamp without time zone.
The npgsql <6.0.0 behavior was to silently discard the offset component but in npgsql 6.0.0, it throws an InvalidCastException.