Find rows using a timestamp column but without the time part

Posts   
 
    
Posts: 62
Joined: 14-Feb-2017
# Posted on: 04-Jun-2024 10:56:19   

Hi,

  • I'm using PostgreSQL
  • I have a table called 'bilan' containing a timestamp column.
  • I would like to be able to compare this timestamp column (converted in DateTime in C#) to a specified date but without using the time part.
  • The query I would like should be something like this
SELECT *
FROM public.bilan LPLA_1 
WHERE LPLA_1.id_planning_bilan_reglementaire = 13
AND CAST(LPLA_1.date AS DATE) = TO_DATE('27/01/2024 00:00:00', 'DD/MM/YYYY')
  • I tried using the following Linq query and using FunctionMappings but can't figure why it's not working.
                var query = from bilan in LinqMetaData.Bilan
                            where bilan.Date.Equals(date)
                            select new BilanReglementaireViewModel
                            {
                                Id = bilan.Id,
                                Date = bilan.Date,
                                ...
                            };

                return query.FirstOrDefaultAsync();
            this.LinqMetaData = new LinqMetaData(this.DataAccessAdapter)
            {
                CustomFunctionMappings = new LinqDatabaseFunctionMappings2()
            };
public class LinqDatabaseFunctionMappings2 : FunctionMappingStore
{
    public LinqDatabaseFunctionMappings2() : base()
    {
        this.Add(new FunctionMapping(typeof(LinqDatabaseFunctions2), nameof(Equals), 2, GetEqualsPattern()));
    }

    private static bool Equals(this DateTime dateInDatabase, DateTime dateInCode)
    {
        return false;
    }
    private static string GetEqualsPattern()
    {
        var dateInDatabase = "CAST({0} AS DATE)";
        var dateInCode = "TO_DATE({1}, 'DD/MM/YYYY')";
        return $"{dateInDatabase} = {dateInCode}";
    }
}

Could you help me ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39709
Joined: 17-Aug-2003
# Posted on: 04-Jun-2024 14:30:40   

What exactly doesn't work, what is the sql generated?

Frans Bouma | Lead developer LLBLGen Pro