filter on day of week

[ghost]

I try to filter a datetime field on the day of the week.
For example (if I only want mondays):
DayOfWeek d = DayOfWeek.Monday;
filter.AddExpression(z => z.mydatetimefield.DayOfWeek == d);
But this result in the following error:
The specified type member 'DayOfWeek' is not supported in LINQ to Entities
Is there already a solution for filtering on day of week ? I use EF 6.1.2 and Npgsql 2.2.4.1.
Thx.

[Emill]

There is no such support in Entity Framework.
You could however take the DiffDays to some known sunday modulo 7 to retrieve the day of week, like this:

DayOfWeek d = DayOfWeek.Monday;

And in your expression:

DbFunctions.DiffDays(new DateTime(1900, 1, 7), x.someDateField) % 7 == (int)d

DbFunctions is in the namespace System.Data.Entity.

[ghost]

I tried this one too, but it returns an error in the generated sql.

c#:
filter.AddExpression(z => DbFunctions.DiffDays(z.Tijdstip, new DateTime(2000,01,03)) % 7 == 0);

Generated sql:
WHERE 0 = date_part('day',date_trunc('day',(('2000-01-03 00:00:00'))) - date_trunc('day',"Extent1"."tijdstip"))::int4 % 7 AND 0 = date_part('day',date_trunc('day',(('2000-01-03 00:00:00'))) - date_trunc('day',"Extent1"."tijdstip"))::int4 % 7

error when executing the query:
ERROR: function date_trunc(unknown, unknown) is not unique
LINE 4: WHERE 0 = date_part('day',date_trunc('day',(('0001-01-05 00:...

Solution:
replace
date_trunc('day',(('2000-01-03 00:00:00')))
by
date_trunc('day', timestamp '2000-01-03 00:00:00')

The where clause generates also 2 times the same expression where 1 should be enough.

How can I resolve this ?

[PeterClaes]

Starting from version 9.4, PostgreSQL supports 2 implementations of date_trunc()

http://www.postgresql.org/docs/9.4/static/functions-datetime.html

date_trunc(text, timestamp) ==> timestamp
date_trunc(text, interval) ==> interval

I assume this is not (yet) supported in npgsql 2..

PC

[Emill]

In Npgsql 3, we prepend "timestamp" to the string so it should work there.
Den 30 maj 2015 02:03 skrev "PeterClaes" notifications@github.com:

Starting from version 9.4, PostgreSQL supports 2 implementations of
date_trunc()

http://www.postgresql.org/docs/9.4/static/functions-datetime.html

date_trunc(text, timestamp) ==> timestamp
date_trunc(text, interval) ==> interval

I assume this is not (yet) supported in npgsql 2..

PC

—
Reply to this email directly or view it on GitHub
#624 (comment).

[roji]

Npgsql 3 has been released so this should be resolved. Please reopen if the problem persists.