Error when using DateTime.Date

[sslaws]

We recently upgraded to EF Core 6 and along with it Npgsql.EntityFrameworkCore.PostgreSQL from version 5.0.10 to 6.0.4 and came across a curious issue. It appears that any query that references a DateTime mapping but also is only interested in the date portion is now returning an error: 42883: function date_trunc(unknown, timestamp with time zone, unknown) does not exist

As part of the upgrade all of our timestamp columns were altered to be timestamp with timezone and all data was already being written to the DB as UTC.

We are currently using postgres:11 (docker container for development).

Details:
When a simple query like

List<UserProfile> profiles = this.dbContext.UserProfile.Where(u => u.CreatedDateTime <= DateTime.UtcNow.AddDays(-10).Date).ToList();

is run It fails with the following:

[2022/05/02 15:11:01]fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'XXX.Database.Context.DbContext'.
      Npgsql.PostgresException (0x80004005): 42883: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
      
      POSITION: 309
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
        Exception data:
          Severity: ERROR
          SqlState: 42883
          MessageText: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
          Hint: No function matches the given name and argument types. You might need to add explicit type casts.
          Position: 309
          File: parse_func.c
          Line: 621
          Routine: ParseFuncOrColumn
      Npgsql.PostgresException (0x80004005): 42883: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
      
      POSITION: 309
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
        Exception data:
          Severity: ERROR
          SqlState: 42883
          MessageText: function date_trunc(unknown, timestamp with time zone, unknown) does not exist
          Hint: No function matches the given name and argument types. You might need to add explicit type casts.
          Position: 309
          File: parse_func.c
          Line: 621
          Routine: ParseFuncOrColumn

This query ran fine previously and on an interesting note, if I change the DB to Postgres 12, 13 or 14 the query also executes as expected.

My current work around is to map date_trunc as a function and explicitly call it but it seems odd to have to do that. We are also looking at upgrading to a newer version of Postgres but that is further out.

Please let me know if this should be posted else where or if you need any additional information.

Thanks

[roji]

This is because of #2333, which was a bugfix in 6.0.4 (the explanation is here). In a nutshell, date_trunc without a time zone (that 3rd argument) takes TimeZone into account when extracting the date, so produces wrong results (.NET DateTime.Date simply returns the Date component without any conversions). Unfortunately that overload of date_trunc was only introduced in PG12.

If you make sure that your TimeZone is always UTC, then it should be OK to map date_trunc as a function, until you upgrade to a newer PG version. It's not an ideal situation, but hopefully it's a sufficient workaround - please let me know if you have more questions.

[sslaws]

Thanks! We will use mapped function until we can upgrade.

[roji]

Closing as there's nothing actionable remaining on the Npgsql side.

[mbirtwistle]

I'm quite surpised this is closed. Use of someField.Date in LINQ queries will be very commonplace, and people will not expect it to break so fundamentally on a 'revision' package version update.
I can confirm it appears that the nuget package support for pgsql versions prior to 12 was effectively dropped in the move from 6.0.3 to 6.0.4, but this is not documented, and is normally the sort of breaking change I'd check for and expect of a 'Major' version update, not from a revision update.

This is the temporary workaround we are implementing on our Dev databases:

CREATE OR REPLACE FUNCTION public.date_trunc(
        p1 text,
        p2 timestamp with time zone,
        p3 text)
    RETURNS timestamp with time zone
	LANGUAGE plpgsql
    COST 1
    STABLE STRICT PARALLEL SAFE 
AS $BODY$
BEGIN
  RETURN pg_catalog.date_trunc(p1, p2);
END;
$BODY$;

[roji]

@mbirtwistle it's indeed unfortunate that a change with this impact needed to get merged in a patch release - this is not something we do lightly. However, it's important to realize that the previous behavior was incorrect, and resulted in wrong results, as an implicit, unwanted timezone conversion could occur. This is the only reason I decided it was acceptable to do this in a patch.

Note that your workaround is only safe if your PostgreSQL database TimeZone is configured to UTC. Otherwise you're reverting to the pre-6.0.4 behavior where implicit, unwanted timezone conversions occur.

I can confirm it appears that the nuget package support for pgsql versions prior to 12 was effectively dropped in the move from 6.0.3 to 6.0.4

That seems very overstated - there's exactly one translation that's affected. Everything else works just as before.