Function date_trunc does not exist-CodePudding

I have a C# query using linq with two datetime fields Im trying to compare. The datetime field in the Postgres DB is stored as timestamp with time zone. However for some reason I am receiving "function date_trunc(unknown, timestamp with time zone, unknown) does not exist" error although in the debug view it shows I am passing the correct parameters. Please note: MyDate field is Nullable

  _context.MyDbObject.Where(a => DateTime.UtcNow.Date >= a.MyDate.Value.Date).AsQueryable();

Query shown in Debug View

(date_trunc('day', now(), 'UTC') >= date_trunc('day', a."MyDate", 'UTC')))

Error

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.

CodePudding user response：

It is not direct answer to your question, but if you care about database indexes, do not use date truncation in queries. Your query can be rewritten.

var currentDate = DateTime.UtcNow.Date;
var endDate = currentDate.AddDays(1);

var query = _context.MyDbObject
    .Where(a => a.MyDate.Value < endDate)
    .AsQueryable();

// or records for current day

var query = _context.MyDbObject
    .Where(a => a.MyDate.Value >= currentDate && a.MyDate.Value < endDate)
    .AsQueryable();