OData / OData/RESTier

Dates in $filter parsed as DateTime with DateTimeKind.Unspecified

Open
#704 2 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.