Dates in $filter parsed as DateTime with DateTimeKind.Unspecified
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 481
- Forks
- 136
- PR merge metrics
- No merged PRs in 30d
Description
I'm working on the breakdance-net5, with the latest postgres provider.
Npgsql 6.0 has a breaking change around timestamp mapping which breaks querying via OData. In order to query a Postgres column of type 'timestamp with time zone', the corresponding DateTime needs to have Kind = DateTimeKind.Utc.
However, all dates in the $filter clause are parsed with DateTimeKind.Unspecified, which causes Npgsql to throw an exception and results in OData returning an invalid and incomplete JSON response.
Setting builder.Select().Expand().Filter().OrderBy().MaxTop(100).Count().SetTimeZoneInfo(TimeZoneInfo.Utc);
Assemblies affected
This behavior is observed using the latest bits in the branch titled "breakdance-net5"
Reproduce steps
Uses a postgres data base with the latest npgsql provider and try to filter an entity by date and time
https://localhost:44307/v_errorlog?$orderby=datetimeinlocaltime desc&$top=10&$filter=(datetimeinlocaltime ge 2022-03-10T16:43:00Z) and (datetimeinlocaltime lt 2022-03-10T16:44:00Z)&$count=true
Actual Result
write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start on the breakdance-net5 branch by reproducing the OData request against PostgreSQL: /v_errorlog with the shown $filter and $orderby parameters. Trace how the $filter dates are parsed when SetTimeZoneInfo(TimeZoneInfo.Utc) is configured. Done means the date values sent to Npgsql have DateTimeKind.Utc and the query returns a complete valid JSON response.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, postgresql
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100