Using DateTime with Npgsql Entity Framework

[neil...@gmail.com]

I have a LINQ query like below:

// Get number of images created in last 48 hours
var imagesCount = ApplicationDbContext.Images.Count(x => DbFunctions.DiffHours(DateTime.Now, x.CreateTime) <= 48);

I get the exception: "Npgsql.NpgsqlException: ERROR: 42883: operator does not exist: timestamp without time zone * interval at Npgsql.NpgsqlState". What does this mean? I googled the exception to no avail. Thanks!

[emil.l...@gmail.com]

Hi. There was a bug in the older version of Npgsql. Please try the latest release candidate and let us know if it fixes your problem.

[neil...@gmail.com]

Hi,

Same problem with the latest release candidate. If it helps, the property in my Images class looks like this:

public DateTime CreateTime { get; set; }

Thanks,

Neil

[emil.l...@gmail.com]

Hi. Make sure you carefully follow the steps like in https://groups.google.com/forum/m/?pli=1#!topic/npgsql-help/IbtUmFMhPgs to make sure you have the latest version.

[emil.l...@gmail.com]

Den lördagen den 16:e augusti 2014 kl. 09:08:19 UTC+2 skrev emil.l...@gmail.com:
> Hi. Make sure you carefully follow the steps like in https://groups.google.com/forum/m/?pli=1#!topic/npgsql-help/IbtUmFMhPgs to make sure you have the latest version.

Also, by looking at your query, you should consider writing it in a slightly different way.

DiffHours first truncates both values to hour-precision, i.e. minutes and seconds are discarded. After that, the values are compared. The first value is subtracted from the second value (not the opposite). To really get the number of images created in the last 48 hours (with seconds-precision), you should write:

var imagesCount = ApplicationDbContext.Images.Count(x => DbFunctions.DiffSeconds(x.CreateTime, DateTime.Now) <= 48 * 60 * 60);

[emil.l...@gmail.com]

You could also try this construct, which I think is the best one:
var imagesCount = ApplicationDbContext.Images.Count(x => DbFunctions.AddHours(x.CreateTime, 48) >= DateTime.Now);

[neil...@gmail.com]

The DbFunctions.AddHours() approach worked, thanks!