Filtering on `DateTime` without timezone information.

[xxpau-eg]

Trying to filter rows based on DateTime column, without considering time zone causes exception.

Assemblies affected

Microsoft.AspNetCore.OData 8.2.0

Reproduce steps

A model like this:

    public class DateAndTimeGridRow
    {
        public int Id { get; set; }
        public DateTime PriceStartLocalDateTimeUnspec { get; set; }
    }

Which is returned from a controller with [EnableQuery] and IQueryable<DateAndTimeGridRow>. Try to get data from the endpoint with an url that contains filtering on the DateTime: https://localhost:44466/api/DateAndTimes?$skip=0&$top=100&$filter=(priceStartLocalDateTimeUnspec%20eq%202024-01-01T10:00)

Expected result

Results from the endpoint. If using EF and SQL Server, I would expect a query that has WHERE PriceStartLocalDateTimeUnspec = '2024-01-01T10:00'

Actual result

{
    "Message": "The query specified in the URI is not valid. The DateTimeOffset text '2024-01-01T00:00' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range.",
    "ExceptionMessage": "The DateTimeOffset text '2024-01-01T00:00' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range.",
    "ExceptionType": "Microsoft.OData.ODataException",
    "StackTrace": "   at Microsoft.OData.UriUtils.ConvertUriStringToDateTimeOffset(String text, DateTimeOffset& targetValue)\r\n   at Microsoft.OData.UriParser.ExpressionLexer.TryParseDateTimeoffset(Int32 tokenPos)\r\n   at Microsoft.OData.UriParser.ExpressionLexer.ParseFromDigit()\r\n   at Microsoft.OData.UriParser.ExpressionLexer.NextTokenImplementation(Exception& error)\r\n   at Microsoft.OData.UriParser.ExpressionLexer.NextToken()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseParenExpression()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimaryStart()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimary()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseInHas()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseUnary()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseMultiplicative()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseAdditive()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseFilter(String filter)\r\n   at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n   at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n   at Microsoft.AspNetCore.OData.Query.FilterQueryOption.get_FilterClause()\r\n   at Microsoft.AspNetCore.OData.Query.Validator.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n   at Microsoft.AspNetCore.OData.Query.FilterQueryOption.Validate(ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNetCore.OData.Query.Validator.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNetCore.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.OnActionExecuting(ActionExecutingContext actionExecutingContext)",
    "InnerException": {
        "ExceptionMessage": "The time zone information is missing on the DateTimeOffset value '2024-01-01T00:00:00'. A DateTimeOffset value must contain the time zone information.",
        "ExceptionType": "System.FormatException",
        "StackTrace": "   at Microsoft.OData.PlatformHelper.ValidateTimeZoneInformationInDateTimeOffsetString(String text)\r\n   at Microsoft.OData.PlatformHelper.ConvertStringToDateTimeOffset(String text)\r\n   at Microsoft.OData.UriUtils.ConvertUriStringToDateTimeOffset(String text, DateTimeOffset& targetValue)"
    }
}

Additional detail

I understand the rationale for that exception (to reduce the likely hood of ambiguity), but in my case - I do store the DateTime in db without the timezone information on purpose. And now, I want to get all rows that match particular filter. E.g.

To give an example (assuming I would store DateTimeOffset) 2010-01-02T20:00+2:00 2010 -01-02T20:00-3:00 2010-01-02T21:00+1:00

My goal is to be able to filter in such a way as to return first two rows when filtering on 2010-01-02T20:00

[habbes]

@xxpau-eg OData does not natively have a DateTime type without offset. So .NET DateTime objects are converted to Edm.DateTimeOffset and vice-versa. If you do not store timezones in your database, then you could configure a default timezone in your OData server and use that timezone in your OData queries, it should return the same result as not having a timezone I think.

builder.Services.AddControllers()
   .AddOData(options =>
   {
       options.TimeZone = TimeZoneInfo.Utc;
      // ...
   }

And then you could run your queries as

?$filter=priceStartLocalDateTimeUnspect eq 2010-01-02T20:00Z

The Z at the end represents the UTC timezone.

If you don't want to include the timezone, you could also break out the date and time components using the date and time functions:

?$filter=date(DateField) eq 2010-01-02 and time(DateField) eq 20:00

What's interesting is that we do actually support filtering against the date without the time component, e.g.:

?$filter=TransactionDate gt 2024-07-02

The exception only occurs when you add the time component.

I think a date without timezone is just as a ambiguous as time without timezone, so maybe we should support something like 2024-07-02T20:00 as well.

Let me know if this helps.

[xxpau-eg]

Thanks for the reply. Yeah, that's what I'll do, just UTC on the server and masquerade the filters as UTC (even though, they are not really). For the filtering on date (e.g. TransactionDate gt 2024-07-02) - I've noticed that as well, but won't use it since in the end it's converted to some DATEPART function calls on SQL Server side - meaning no index usage. And this happens even if I have DateOnly on the model side.