WebApi 🚀 - OData V4 service should support DateTime

On _2014-07-23 11:50:40 UTC_, GuilhermeM commented:

The OASIS decision clearly shows how strictly academic and disconnected from reality the group seems to be.

Dropping support for DateTime because of potential timezone issues is the same as dropping support for String because of potential encoding issues. Plain nonsense.

Regardless of this clear bad call from the spec group, the ASP.NET Web API stack must make the protocol usable again by providing automatic and transparent DateTime to DateTimeOffset translations or even better, overriding the spec and doing the right thing
of supporting Edm.DateTime as per all previous versions.

LianwMS on 12 Jan 2015

👍6

On _2014-07-23 21:41:27 UTC_, cquick commented:

If there is a desire to support datetimeoffset, then fine. But we need guidance on using Entity Framework with SQL and gracefully dealing with this change. Until then, it appears I will not be able to use OData 4 with any new projects. That's just too
bad.

LianwMS on 12 Jan 2015

On _2014-07-28 23:54:05 UTC_, TimMc commented:

I personally am all for using DateTimeOffset instead of DateTime, but strict implementation was stupid and needs to be reversed.

Sometimes need to store SQL date datatype, which is not supported by DateTimeOffset.
Some people don't want to store with offsets since its unnecessary in their implementation. All this change did was force them to bloat the db.

LianwMS on 12 Jan 2015

On _2014-07-30 10:49:29 UTC_, schaibaa commented:

Agreed... stupid change. Can't believe this made it's way through. I bet less than 0.001% of all dates are stored as datetimeoffset - and expecting everyone to convert millions of rows is dumb.

LianwMS on 12 Jan 2015

On _2014-08-01 05:15:15 UTC_, BullCreek commented:

Hooray - I was complaining about this back in April - at least now I don't feel like the only one who thinks it was a boneheaded move. To reiterate my concern from the original thread - in addition to causing developers a lot of grief mostly for no reason

there is also the huge problem that SQL Server 2008 and later are really the only DB providers that support the semantics of DateTimeOffset - yet I read that ASP.NET vNext and oData are supposed to be all about openness - meaning that I could/should be able
to run it on Mono and/or use MySQL or Postgres as the DB - but alas those databases and their respective providers have no clue what a DateTimeOffset is - not to mention lots of legacy SQL Server databases that are still required to be version 8 or version
9 and also don't support DateTimeOffset.

That said, other than this issue - v4 and the functionality it exposes is a huge improvement over previous versions - too bad few can/will use it because of this limitation.

LianwMS on 12 Jan 2015

On _2014-08-01 20:29:14 UTC_, goroth commented:

UserVoice request:
http://aspnet.uservoice.com/forums/147201-asp-net-web-api/suggestions/6242255-odata-v4-service-should-support-datetime

LianwMS on 12 Jan 2015

On _2014-08-10 03:19:47 UTC_, RoryPS commented:

+1
If there's no support for DateTime then just provide the option to map all DateTime values automatically to DateTimeOffset for a particular timezone, probably defaulting to UTC. That would once again make OData usable for the 99.999% of the world's databases
that don't use DateTimeOffset.

LianwMS on 12 Jan 2015

On _2014-08-12 05:46:01 UTC_, Vaccano commented:

Why care if a developer uses DateTime? Sometimes I just need a DateTime.

Sure support DateTimeOffset. Give full tools to use it and all it has to offer. But leave DateTime in there too.

Pushing developers out of DateTime and into DateTimeOffset is the same as trying to push developers out of Integers and into Doubles. In both scenarios you have a data type that offers more precision than the basic data type. But sometimes you just don't need
that extra precision.

Also, there is a huge amount of existing code out there that will not (or cannot) be upgraded to DateTimeOffset.

LianwMS on 12 Jan 2015

On _2014-08-12 16:32:47 UTC_, MacOkieh commented:

Stupid decision that makes it very difficult to move from v1-3 to v4. It is ok to support DateTimeOffset, but why drop support for DateTime at the same time? Means more code for me and LOTS of work to upgrade older projects (if not impossible at all).

LianwMS on 12 Jan 2015

On _2014-08-19 21:46:25 UTC_, millman commented:

The product I work on has to support SQL Sever 2005 as long as Microsoft does. SQL Server 2005 doesn't support the DateTimeOffset column type so DateTime support needs to be brought back. This was a glaring oversight!

LianwMS on 12 Jan 2015

On _2014-08-25 22:49:07 UTC_, gsulcer commented:

Please add this back in. Odata v4 has many advantages, but now I have to abandon my upgrade attempt. This decision forces many applications to remain on v3 forever. As an enterprise developer, working with many legacy systems, Odata is a great solution
for creating common APIs into those legacy systems. They cannot be changed.

I thought the "O" in "OData" stood for "Open". I guess it stand for "Only One Way".

LianwMS on 12 Jan 2015

On _2014-09-02 21:33:26 UTC_, Mikal commented:

Please make OData for Web.Api able to use DateTime behind the scenes, possibly by making the developer specify a conversion technigue (assume UTC or local time).

I can live with a data transfer protocol making demands on the way data is serialized. But assuming that DateTimeOffset is always better for our datastore, and forcing us to use it over DateTime, is stupid and misguided. The majority of the time I need to store
a date, I'm not interested in the timezone information.

LianwMS on 12 Jan 2015

On _2014-09-03 07:55:00 UTC_, johncrim commented:

We have use-cases (eg time description in TV), where we have to provide our own definition of what a point in time is relative to - eg local time, broadcast time, or UTC. Local time means the point in time (if measured in UTC) changes as the TV market
changes. Broadcast time is like local time, but the new day starts at 6am instead of midnight - and to make matters worse, each network can have a different definition for when the new broadcast day starts.

In this use-case, the imprecision of DateTime is much more correct than the false precision of DateTimeOffset. As a platform vendor, you should provide both (as the rest of the .NET framework has done) and let the developer decide which is the best fit for
their problem.

LianwMS on 12 Jan 2015

On _2014-09-06 03:35:42 UTC_, johncrim commented:

Separately, changing to DateTimeOffset, even if feasible, requires support in other systems. Eg DateTimeOffset does not work with the EF MySQL connector -
http://bugs.mysql.com/bug.php?id=73788

LianwMS on 12 Jan 2015

On _2014-09-08 03:04:45 UTC_, johncrim commented:

I pushed a fix for this on my fork:

https://aspnetwebstack.codeplex.com/SourceControl/network/forks/johncrim/datetimefixes

Note that I changed the unit tests from asserting that use of DateTime throws, to asserting that use of DateTime does not throw. I did have a problem with 3 unit tests, in that I couldn't easily figure out how to pass in a modelBuilder to Moq - now that the
DateTime doesn't throw in parameter validation, other code throws b/c the modelBuilder is null. So I skipped those 3 tests. After that, everything passes.

I can also push a nuget package if doing so is allowed...

LianwMS on 12 Jan 2015

On _2014-09-15 20:37:56 UTC_, Kittoes commented:

Would allowing the usage of the tag [Column(TypeName = "date")] on DateTimeOffset properties be a simple solution to this?

LianwMS on 12 Jan 2015

On _2014-10-09 23:25:20 UTC_, jonagostinello commented:

Even the work arround fail
when trying to filter or orderby
http://localhost:18832/Aas/Activities?$top=11&$orderby=CreatedAt
using this solution http://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/

{
"error":{
"code":"","message":"An error has occurred.","innererror":{
"message":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; odata.metadata=minimal'.","type":"System.InvalidOperationException","stacktrace":"","internalexception":{
"message":"The specified type member 'CreatedAt' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.","type":"System.NotSupportedException","stacktrace":" at

LianwMS on 12 Jan 2015

On _2014-11-17 21:29:09 UTC_, markrendle commented:

Unbelievable disregard for the real world and customers' actual requirements. We're supposed to make sweeping changes to production databases just because some standards group who never actually implement anything themselves get on their high horses about
date types? Absolutely f-ing ridiculous.

LianwMS on 12 Jan 2015

On _2014-11-18 07:11:36 UTC_, jonagostinello commented:

As to what I wrote earlier about the 2 workarounds from
http://stackoverflow.com/questions/25189557/how-to-get-web-api-odata-v4-to-use-datetime
http://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/

neither work. You can no longer filter or sort by these fields or get this error

http://localhost:18832/Aas/Activities?$top=11&$orderby=CreatedAt Gives this error "code":"","message":"An
error has occurred.","innererror":{
"message":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; odata.metadata=minimal'.","type":"System.InvalidOperationException","stacktrace":"","internalexception":{
"message":"The specified type member 'CreatedAt' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.","type":"System.NotSupportedException","stacktrace":"

But this works as it is addressed indirectly
http://localhost:18832/Aas/AppUsers%28102%29/AppUserActivities?$expand=Case&$filter=%28Reminder%20ne%20null%20and%20IsComplete%20eq%20null%29&$top=15&$orderby=Reminder&$count=true
Reminder Iscomplete are from activity from AppUserActivities.

This is wierd. Does anyone have a solution?

I connect to mysql. You bunch of idiots. There is not a datetimeoffset.

And everyone wonders why no one wants to develop for Microsoft Technologies.

LianwMS on 12 Jan 2015

On _2014-11-19 05:19:32 UTC_, goroth commented:

Based on
https://issues.oasis-open.org/browse/ODATA-220 the issue is being "DEFERRED" until "V5.0".
Wonder when "V5.0" is going to be released so that we can use OData again?

LianwMS on 12 Jan 2015

On _2014-11-29 00:22:13 UTC_, _kj_ commented:

We migrated from WCF DS to Web Api OData and regretfully found that absense of DateTime support is a really big problem. So we decided to make one more fork with DateTime fixes, and wait until DateTime support will return in future releases.

So, here is
https://aspnetwebstack.codeplex.com/SourceControl/network/forks/_kj_/odata53datetime?branch=odata-v5.3-rtm

$orderby, $filter, function parameters/returns are supported.

LianwMS on 12 Jan 2015

On _2014-12-02 21:04:16 UTC_, goroth commented:

Here is a fork for 5.3 that supports DateTimeOffset.
https://aspnetwebstack.codeplex.com/SourceControl/network/forks/_kj_/odata53datetime?branch=odata-v5.3-rtm

LianwMS on 12 Jan 2015

On _2014-12-02 21:05:08 UTC_, goroth commented:

* I mean DateTime *
Here is a fork for 5.3 that supports DateTime.
https://aspnetwebstack.codeplex.com/SourceControl/network/forks/_kj_/odata53datetime?branch=odata-v5.3-rtm

LianwMS on 12 Jan 2015

On _2014-12-04 17:37:18 UTC_, DotNetWise commented:

M$, stop doing bad decisions like this! Breaking changes to so common stuff makes everyone run away from .NET / Microsoft and go ahead with other more reliable technologies!

Deferring it to ODATA 5.0 and not fixing it in so many months makes it really moving away and not trusting ODATA anymore!

LianwMS on 12 Jan 2015

@xuzhg Does your commit https://github.com/OData/WebApi/commit/2717aec772fa2f69a2011e841ffdd385823ae822 mean that datetime is now supported?

ImGonaRot on 19 Jan 2015

@ImGonaRot Yes.

xuzhg on 23 Jan 2015

While you can perform some filtering on a DateTime field, it looks like attempting to filter by a full date does not work because the string value is cast to DateTimeOffset. So for example:

http://localhost/entity?$filter=DateTimeField eq 2015-01-01

Returns an error:

The binary operator Equal is not defined for the types 'System.Nullable1[System.DateTime]' and 'System.Nullable1[System.DateTimeOffset]'

I've done some exploration and have a few options for work arounds, but I wanted to be sure I'm not missing something obvious. Is there a particular format we should be passing the date in as?

BrentSouza on 26 Jan 2015

It looks like the OData Uri parser is mapping the DateTime column back to DateTime for Linq, but leaving the literal as a DateTimeOffset. I tried using the date() Odata function but got a NotImplemented Exception. I see the date function issue is targeted for v5.6. Seems like either the type mapping or the date function need to be fixed to make v5.4 DateTime support usable.

vgpardue on 28 Jan 2015

... or instead of using the date function I could cast both sides of the comparison to Edm.Date. I guess there's that...

vgpardue on 28 Jan 2015

@BrentSouza and @vgpardue

If V5.4, you can use year(), month(), day(), hour() ... on the DateTime property in the filter expression.
However, date(), time() will be supported in the V5.5 along with the supporting of Edm.Date & Edm.TimeOfDay. Of course, the filter on DataTime property will be covered also. Thanks.

xuzhg on 29 Jan 2015

Thanks. I had noticed that year(), month(), and day() worked. But cast() to Edm.Date also seemed to work.

Sent from Surface Pro

From: Sam Xu
Sent: ‎Wednesday‎, ‎January‎ ‎28‎, ‎2015 ‎10‎:‎51‎ ‎PM
To: OData/WebApi
Cc: Gary Pardue

@BrentSouza and @vgpardue

If V5.4, you can use year(), month(), day(), hour() ... on the DateTime property in the filter expression.
However, date(), time() will be supported in the V5.5 alone with the supporting of Edm.Date & Edm.TimeOfDay. Of course, the filter on DataTime property will be covered also. Thanks.

—
Reply to this email directly or view it on GitHub.

vgpardue on 29 Jan 2015

@xuzhg
Thanks for the information. I worked around it by modifying the FilterBinder.CreateBinaryExpression method, adding an additional check before the check for type equality between the left and right expressions. If the left type is DateTime and right type is DateTimeOffset, I modify the right expression to access the DateTime property of the DateTimeOffset:

``` c#
if (left.Type == typeof(DateTime) && right.Type == typeof(DateTimeOffset))
{
right = Expression.Property(right, "DateTime");
}

if (left.Type != right.Type)
{
// one of them must be nullable and the other is not.
left = ToNullable(left);
right = ToNullable(right);
}
```

Doing it this way avoids any casting at the database level which avoids potential performance problems. Additionally, SQL Server will cast a DateTime to DateTimeOffset using 0 as the offset, and not the local offset, which is what .NET does, so you avoid getting incorrect results due to the mismatch in time conversions.

BrentSouza on 29 Jan 2015

Move to 5.6 and let's see if there is more issues found about DateTime.

xuzhg on 30 Mar 2015

please file new issue if you have more concerns. Thanks.

xuzhg on 22 Apr 2015

I have a server running in france, all datetimes in the db clearly don't have timezones so the assumption is zulu time (GMT) ... when i get results back that include entities with datetime properties they are datetimes with 1 hour removed to account for the time difference between gmt and the servers local time ...

I don't think this should happen, since the time zone is not known I believe OData should assume GMT when serialising.

Or is it possible to tell odata "when you see a datetime assume X offset"?
Some guidance please.

TehWardy on 13 Jan 2016

My whole company stopped writing new stuff in OData because of this issue. We need DateTime. DateTimeOffset does not work.

I wonder if there was a general loss in adoption for OData because of this shortsighted decision?

Vaccano on 17 Apr 2017

I see this has the "backlog" tag. Dare I hope that there is a plan to deviate from the spec and add DateTime back in?

I so hope that this means real DateTime support is coming back! I would love to be able to lead my company back to using OData!

Vaccano on 6 Feb 2018

👍1

I opened this ticket back in 2014 and almost 4 years later... MS still does not support DateTime in OData.
Two of the largest 3rd party control vendors (DevExpress and Telerik) don't support DateTimeOffset.
This is sad. OData V3 worked perfect with DateTime and then it was removed from OData V4 and it does not look like it will make it into OData V5.

ImGonaRot on 27 Mar 2018

👍2

@mikepizzo

xuzhg on 27 Mar 2018

Pinging this again to see if there is any comment.

I still would love to be able to use OData again, but I can't use DateTimeOffset. There are many times that I don't know the Time Zone.

Adding one implies a precision that I don't have! It would not be responsible as an API creator to just add local timezone information, implying that the timezone was in fact local, when it could have been anywhere in the USA.

Please consider that there are more complex cases out there than twitter clients and other closed systems. Please deviate from the spec and add in true DateTime support!

Vaccano on 30 May 2018

From what i can see V6 now supports DateTimes.
However i think older versions also map datetimes as datetimeoffsets too ...

http://odata.github.io/WebApi/datetime-support

... essentially this is a well and truly solved problem ... I just wish the team would be more communicative in letting the community know when they do something right :)

That said ... there is a link to the closed ticket above that shows how they fixed it.

TehWardy on 30 May 2018

@TehWardy - Sadly no. Mapping to DateTimeOffset is nice for some cases.

But many times you don't control the types of data in the source of your information. In my case I get information from over a thousand different systems around the USA. None of which I control.

None of these systems give me a timezone! So converting the dates I get from those systems to a DateTimeOffset implies a precision that I don't know.

For example: I am getting DateTimes from New York to California and everywhere in between. To use OData, it will require me to convert those DateTimes to DateTimeOffsets (effectively adding a TimeZone). I live in the Mountain Timezone. If I have OData add the Mountain Timezone to all my DateTimes (to make them DateTimeOffsets) it is much much worse than just showing a DateTime. Now consumers of my API think that it was in fact a Mountain Timezone DateTime that was obtained from the source system (when it could have been from a Pacific or Atlantic timezone). This implying of a precision I don't know (TimeZone) can cause problems that would not have happened had I just provided the DateTime.

The choice to remove DateTime in favor of DateTimeOffset was shortsighted. There are so many systems that don't use DateTimeOffsets. To force DateTimeOffset when you don't know the timezone is like adding decimal digits after a integer just because you have to.

In so many scenarios you can't know it is correct!

Vaccano on 30 May 2018

@Vaccano Here is the thing you might be missing in this equation: The human concept of time always exists in a time zone, even if that information didn't get passed on in the formatting. If you a) don't know what time zone the time is happening in, and/or b) can't guarantee that it's correct, then I hate to break it to you, but the system that is broken in this equation is not OData, and here's why:

APIs should ALWAYS be dealing with dates in UTC and then calculating the difference in local time in the client app. Your servers should have their system time set to UTC, and you should be saving your DateTimes as UTC, (which means storing them in +00:00.).

It's a separation of concerns that should be well-and-truly enforced. If you are storing time data in your server's time zone (Mountain, I'm assuming), then what happens when you move? Your time data becomes inconsistent and therefore invalid.

The spec is as it should be, and they have implemented it properly. In all honesty, you should give up and move on, because the reasons for dealing with Time data in the Z format for APIs is very well documented, and extend beyond just WebAPI and OData.

@ImGonaRot Not sure what issue you are experiencing, but I've been using Telerik controls with BOTH OData v3 AND OData v4 and they work just fine with DateTimeOffsets. Are you sure you're using kendo.aspnet.js properly?

robertmclaws on 30 May 2018

👍1

@robertmclaws Neither the Telerik nor DevExpress (Win form or Web form aspx) support DateTimeOffset.
Not only that but DateTimeOffset is a Microsoft standard not a web standard.
I will admit that javascript based controls HTML5 grids from Telerik (Kendo) and DevExpress work fine if you also have moment.js handy on site.
This is assuming that we store our date times in MSSQL with SQL DataType DateTimeOffset and are not using UTC dates in SQL.
If we stored all dates in UTC then we have other issues like reports and such.
Microsoft in OData V3 supported pure DateTime but removed it in V4.
It would be nice to have the best of both worlds.
As of now, I can't use WinForms controls or ASPX controls from any 3rd party company out-of-box for the "proxy" model generated from OData V4.
AKA the OData client Microsoft created.
I have spent the last 5 years creating "work arounds" when using 3rd party controls against the OData V4 service.

ImGonaRot on 30 May 2018

@robertmclaws - OData did not even support DateTimeOffset till V2/V3 (can't recall for sure). In 3 short years it went from no support for DateTimeOffset to fully removing DateTime and only supporting DateTimeOffset.

Both extremes are wrong.

DateTime is a native type of almost every system and language on the planet. It, or something like it, has been in use since almost the dawn of programming.

DateTimeOffset is a, relatively, new and improved way to record Date/Time. And if your systems can support it then GREAT! You should totally use it.

But to think that every system has been rewritten to use DateTimeOffset is wrong. These systems where written without DateTimeOffeset and have been able to function great for years and years. To suppose that they could not continue to function correctly is a "Perfect World or Nothing" kind of thought process.

BOTH should be supported. It does not hurt those that can use DateTimeOffset to have support for DateTime. But having both allows the use of the framework by more than just newer projects.

Vaccano on 30 May 2018

For some historical background:

In the OData standards group we spent a lot of time discussing DateTime vs DateTimeOffset. While we understood that DateTime has been around longer than DateTimeOffset, we saw inherit issues with propagating its use in a global environment. As @robertmclaws says, DateTime without offset is meaningless, and prone to mis-interpretation. DateTimeOffset was invented to fix this, but many systems continue to support DateTime for historical reasons, pushing the problem to down-level callers, which just propagates the problem. While recognizing the difficulties in supporting DateTimeOffset in current environments that continue to have DateTime storage for historical reasons, we wanted to optimize OData for the caller by taking a hard stand, forcing services to handle the DateTime issue, rather than push the problem down.

Admittedly, this has not always been a popular stance, and we have revisited the decision several times in the technical committee. Each time we have reconfirmed the benefit of being unambiguous versus the difficulty supporting an offset in certain environments.

We are always open to public feedback on the standard.

I would also love to have a discussion on how we can best support that standard in our libraries; in an environment like ours, where DateTime does exist, how can we support mapping those DateTime values to DateTimeOffset at the protocol level?

mikepizzo on 31 May 2018

@mikepizzo - Of course the privilege to make that choice rests with the standard makers.

And I understand why the choice was made. It was to provide a "pit of success" for those who are making APIs that don't have to integrate with the wider world, or if they do, have the luxury of integrating with newer APIs. By removing the choice for them, you force the right decision on them.

I think that kind of thinking leads to a very narrow compatibility matrix. I think as a REST Framework, OData should be compatible to as wide an audience as possible. It then requires the developers that use the framework to make the correct choice on how to represent their dates and times (along with the rest of their data types).

I really do love the OData protocol/standard. If you can support its forcing of timezones, then I believe it is the best REST based framework out there. I think that is why is so frustrating to me that it dropped the support for DateTime. Before that happened I was a total fanboy of OData and had implemented it into one of my company's core systems. (I was planning to try to push it into rest of my company's standards as well.)

Sadly, I can't make all my companies clients (over a thousand) change how they send us data. We have used DateTime effectively for 15+ years (which means to me that it is clearly not "meaningless"). The cost of the upgrade to our clients and my company is a non-starter when it offers no additional business value. (We did have a meeting about it with our management, but it was almost unanimous decided that it would be almost impossible to get implemented in most of our client's Hospital and Laboratory Information Systems.)

I see now that the update to "backlog" status of this PBI is not what I thought it was. I was hoping it was a consideration of breaking with the standard in order to be able support more systems.

I will go back to being sad that I can't use OData and having my developers just roll their own REST APIs...

Vaccano on 31 May 2018

@vaccano, I'm just having a hard time seeing why this is such a problem. There are other ways of solving the data-entry problem, depending on how the data is coming in and out of the system. If other parties are submitting data to you through the OData endpoint, why don't you make the date field on the input methods a string, and just parse the string in the POST method and convert it to a DateTimeOffset for storage? Then, in your read cycle, return the DateTimeOffset property too, but make the DateTime property just DateTimeOffset.ToString() in the format they need it? I have to do the same thing for Geography data types when the clients that connect to my library don't support DbGeography.

Also, you misunderstand the concept of "meaningless" when it comes to DateTime. The problems with the DateTime object are well-documented, and there are libraries like NodaTime that were invented to work around them. The way you are storing time data makes it difficult to discern data in real-world situations where you need to derive equivalent meaning between records, because without TimeZone information,DateTime is ambiguous and therefore has less meaning that can be derived from it.

robertmclaws on 31 May 2018

@robertmclaws Sadly it is much more complex than simple data entry. Here is one scenario (just one) of the data flow of one (just one) of the DateTime fields in my system.

A nurse draws blood from a patient at a clinic somewhere in the United States. The DateTime value in this scenario is the date and time that the blood was drawn from the patient. The nurse enters the blood draw time and date along with the blood test to be performed into their computer system.

That clinic then sends the physical blood and the digital representation of the test to be performed and blood collection date time (it can be paperwork instead) it to be tested at a central laboratory (my company's client). In this scenario, the clinic's central lab cannot do the test. So they have their system send the test to my company (along with the physical blood). (These computer systems are very large systems that are usually hard to change/customize. Think of them like an ERP.)

While it is possible that some of the clinics or central laboratories are transferring a time zone along with the date and time, most don't.

And most importantly, the vast majority of the time, the time zone doesn't matter. 3-4 hours either way will not make a difference in the stability of the blood for the test that is needed to be performed.

There are a few cases where the time zone can matter. And when it does we are very careful to make a phone call (or otherwise figure out the timezone) to ensure that the blood is still testable. But the cost of that call in those few cases is not even close to the cost of trying to get all of our clients (and the clinics that send to them) to start supporting time zones.

Because again, the vast majority of the time, the time zone just doesn't make a difference.

The API in this scenario (that I would love to use OData v4 for) provides internal access to the blood collection DateTime (along with many other values). Because I don't know the timezone, just adding one could cause us to make a horrible mistake when determining if a phone call needs to be made.

The consumers of the API need to have the actual value that the nurse entered. If I just convert it to Mountain Timezone (or some other timezone), then my API is lying. That could cause a call to not be made (that should be) and an test performed (that should not be) and an erroneous result delivered. Bad test results can lead to loss of life.

In short adding time zone information is adding a time precision that I don't know. One that would be very very expensive to be able to know.

This is why I keep saying removing DateTime was a shortsighted decision. It is a decision that makes a minor architectural decision easier for APIs that are in green field scenarios or are integrating with new(ish) public APIs like Twitter and Facebook .

But it completely shuts down so many real world scenarios of business to business transactions that can't get or don't need the time zone precision.

Vaccano on 31 May 2018

@Vaccano I think you should re-read my post because I gave you a workaround that would apply to your situation.

robertmclaws on 31 May 2018

@robertmclaws The workaround does not work when data binding to grids (Winform or ASPX) using 3rd party controls. The grid thinks it is text / string and will not show a date time picker as the editor.
Also you can't query the odata service correctly to filter by date if you are only exposing a string for the date.

ImGonaRot on 31 May 2018

Yes you can, if you implement the getters and setters properly. In WinForms you can also use a Model that inherits from your Client object and does the conversions for you.

robertmclaws on 31 May 2018

@robertmclaws You can't implement your own getters / setters and get LINQ to work properly when querying the OData service.
The OData libraries don't know how to convert LINQ queries into OData "URL" queries when using custom get / set.
Both Telerik and DevExpress have their own custom OData Data Source and neither of those data sources work with querying the OData service when you implement a DateTimeOffset as a String.
While I can write my own custom data source, I would lose out on things like built in paging and custom filters the both Telerik and DevExpress have already written that can convert those into OData query syntax.
OData V3 was much easier and worked with all my 3rd party controls out of the box without generating tons of extra code.
All I had to do was generate a proxy using the OData Client generator and then it just worked.
With OData V4 I still generate the proxy using the OData Client generator but I have to add lots of extra code on the client side to account for DateTimeOffset.
The point is that I am tied to 3rd party controls and if I have to "extend" my classes by creating fake properties or extend controls, it is not saving me time and is not worth the effort to move to OData V4.
I do agree that DateTimeOffset is nice but I also agree that I still need to support DateTime "while" I try to transition to DateTimeOffset.

ImGonaRot on 1 Jun 2018

Where i get annoyed is that UI frameworks refuse to support datetimeoffset because they build for multiple back ends and Microsoft seems to be the owner of the datetimeoffset definition.

Personally though i can see a situation for just collecting or returning a date or a time and not a whole datetime and the fact that I can't seem to declare either is baffling to me.

for example ... when an invoice is created is typically a point in time (represented by a datetimeoffset but it's common to refer to the creation date of an invoice as well ... exactly that ... it's "Creation DATE".

This debate has come about because the whole ecosystem in this area needs reworking.
The way something is rendered or bound to a UI should be subjective and based on business context but the way something is stored is not a subjective matter.

My issue is that I am forced to always add the precision of a datetimeoffset no matter my situation of reason for the field in question.
I would prefer a means to declare in the odata model "this field is just a date" and have it return just a Edm.Date instead of being forced to use datetimeoffset in all cases and then be forced to make assumptions about historical data that cause me as a programmer to code in a potential inaccuracy in the value returned purely because the information isn' there and a standard forces it to be.

The suggestion above of using strings also wont work because date logic would then not be able to be added to fields during queries so there has to be some means of working with a date & time aware object.

As stated above somewhere ... this need to force the extra precision is like not supporting integers and forcing decimal values on the entire api layer ... the mind boggles!

TehWardy on 12 Jun 2018

@Vaccano in your case ...
so you have an api that is accepting datetimes?
are these values a representation of a point in time from the source system or just a date on which an event occurred.

I find this distinction important but agree that your api should ALWAYS store utc data and do the conversion internally if one is required.
I have found ... much like you though ... there's a difference between "this happened on this date some 500 years ago" (which requires no precision) and "this invoice was created at this point in time" which depending on context may need the precision or may not.

I think it's good that the WebAPI guys and the Oasis guys are trying to promote good working standards and everything is workable ... but it may often result in assumptions.

In your case ... the caller could be in any timezone, so if they say "here's a datetime value" how do you translate that?
This to me is the biggest problem we have with OData ... there's no way to easily in the API determine the time zone the caller means when they don't provide it ... so the oasis answer is "force them to provide it or assume UTC".

That of course means forcing an entire industry of legacy systems to start behaving ...
oh dear ...
Did these guys ever work in IT ?

lol

TehWardy on 12 Jun 2018

@TehWardy - I have kind of given up on this (really I gave up on OData several years ago). I got my hopes back up by misinterpreting one of the their tags.

It is kind of a lost cause because @mikepizzo and the others that control the project have made up their minds on this. And not enough people voice a dissenting opinion to make a difference.

But I agree that it seems they have never had to work in a real enterprise environment. OData's design decisions are centered around simple systems doing simple communications. Mostly it assumes that the OData system is the source/owner of the data that it provides.

OData could do so much more, but alas, they have decided to limit it so that a few lower end developers don't have to make one data type decision.

Vaccano on 13 Jun 2018

I truly appreciate this thread because, contrary to how it may appear, I agree that this is an issue and I don't yet understand how best to address it.

The OData TC made a very hard decision to remove DateTime when adopting OData as an ISO standard, and I've tried (as impartially as possible) to convey the thinking behind that decision.

While I believe in the reasons for making this decision, I also believe that we need to be able to support accessing existing systems, and I honestly want to understand these scenarios to see if there are options for supporting the type in the framework without generating payloads that violate the ISO Standard.

I provided the feedback link for the OData TC because that is where we need to go to make a change to the protocol. Separately, I am open to suggestions for supporting DateTime without violating the current protocol.

As an aside, note that OData V4 did add Date and TimeOfDay types to satisfy common scenarios where the timezone is not relevant.

mikepizzo on 13 Jun 2018

@mikepizzo I original opened this issue back in 2014 on CodePlex when I had first moved from OData V3 into OData V4.
"https://archive.codeplex.com/?p=aspnetwebstack -- OData V4 service should support DateTime"
Our company adopted the OData standard even back when it was just a WCF extension (AKA Data Services).
Using proxy generators with WCF and importing those into both MS WinForms and MS WebForms was a breeze up until V4...
In V4 we have been working with both Telerik and DevExpress on trying to get them to support DateTimeOffset for that past 4 years with little to no luck.
I have several open tickets with both companies on DateTimeOffset.
I personally think DateTimeOffset is a great way to move forward but like most developers, I am tied to the controls I use.
We are currently getting by some of the issues when posting the data to the service by using the "SetTimeZoneInfo" in the WebApiConfig but that is only upstream.
We also have several OData Functions and Actions that can do the Date conversions on the server side when talking to the controllers.
For client side we have been able to get by most issues by using partial classes that are on top of the proxy generated classes and doing DateTime to DateTimeOffset conversions inside the getters / setters.
But that only gets us so far down the road and does not work well when using LINQ to OData queries.

OData V4 Date is good if you don't need the time but we need the time and I have yet to find a good use for TimeOfDay.

I know that SAP has adopted some of the OData standard. I wonder what issues they are facing with DateTimeOffset if any at all?

ImGonaRot on 13 Jun 2018

@mikepizzo - I personally don't see any way to make it work in the current OData standard.

That is why I said the choice to abandon DateTime was shortsighted.

I thought long and hard back when you cut out DateTime on how I could find a way around the issue and still not have to lie to the consumers of my APIs.

As I said, I was a total OData fanboy back then and had planned to get my whole enterprise using OData for our SOA implementation. So the breaking change to make OData incompatible with our enterprise was a VERY hard pill for me to swallow. I really tried HARD to find a way around the issue. (Creative uses of Date and TimeOfDay, documentation to tell everyone to ignore the timezone, forking the project and trying to maintain my own version, using strings, etc)

But every solution was just too much work to add to the "cost" of OData when trying to convince my management that OData was the product we should use for our REST services.

I firmly believe the only way to fix this is to readdress this with the standards team and get DateTime added back in.

I think OData will find a significant upswing in adoption if it decides to support DateTime. As I said before, pretty much every programming language and database in existence supports DateTime. By looking at compatibility a bit more, I think OData has the potential to become the defacto REST standard. (And would have been already, but for the missteps with V4.)

Vaccano on 13 Jun 2018

@mikepizzo I agree with the others here ...
Whilst having a standard is great this particular standard (the OData one) is short sighted and doesn't resolve the fact that the real world isn't perfect and often API providers don't control both ends.

I urge you to consider the following scenarios ...

The unknown assumption
A remote system sending a "date" that the current implementation can parse in to a DateTimeOffset object.
due to the way in which data is transferred to the OData service (as JSON) there's no way to tell the source data type or timezone we simply have to assume and build potentially incorrect precision information to make this work.
1. The precision enforcement issue
  An API being given data without precision should never imply it or force it to be there, doing so inherently makes the data received incorrect (in law).
  In my situation I'm building out cloud based API's that receive invoice data ... what will i ever do if there's a legal battle about a set of invoice data where the data sent does not match the data in the db (which is what happens due to point 1 unless I accept the post as a raw string then parse away from OData).
2. UI frameworks: binding issues
  I like many others use frameworks like Telerik's jquery based control library and find myself constantly having to "map" or "parse" or "side step" or "assume" some conversion issue about the data because of the problems introduced from this situation.
  Not having to fight that would take literally months off of the hours i've already put in to UI work on dates, times, and points in time
3. UI frameworks: querying issues
  A date cannot be asked a time related question, doing so makes no sense.
  A time cannot be asked a date related question doing so makes no sense.
  So by logical deduction ...
  A point in time created from either has a set of questions that when asked make no sense.
  I always both send and receive strings (a fact of http comms and serialized messaging) so my metadata should be able to explain the subset of questions that make sense or how a DateTimeOffset should be correctly interpreted if no support for the correct data type is added.

A suggestion:
I would start by allowing the model to contain all 3 of the types above and some guidance of how they can be used (bound to, transferred, correctly and accurately interpreted), and here's why ...

The fact is systems out there use both DateTime and DateTimeOffset, and this is a .Net implementation of a framework sat on top of a framework that supports both ... to remove either is bad on idea purely on that basis.
Yes OData is a clearly well defined standard but the WebAPI implementation should also be willing to accept (like browsers do with the global standards for html) that it can vary from the standard for valid reasons.

I worded the end of point 3 very precisely that way to highlight a point that OData, .Net and WebAPI all have failed to address since their invention ...
There is call for data types from the whole web stack to handle:

A date (nothing that I know of handles this)
A time (system.TimeSpan can handle this)
A precise point in time (System.DateTimeOffset handles this)

If a remote system in an unknown timezone sends me a serialised DateTime string because that's how it's defined in that system I need to be able to receive that AS IS ... by inferring any difference in the data I could be breaking the law since Invoices are a form of legal document and thus NOT open to interpretation.

TehWardy on 13 Jun 2018

Thanks, everyone, for the feedback.

I brought this up at our weekly OData TC meeting today, and we discussed a few different mechanisms for allowing a service, particularly one that integrates with a legacy system, to convey the fact that a DateTime value has no offset information (without encouraging its use for new systems). Part of the challenge is introducing this without breaking existing OData V4 clients.

One option that we discussed is defining a core "DateTime" annotation that could be applied to a string to say "this string represents a DateTime value with no offset". We could take this one step further, and define a core TypeDefinition for DateTime whose underlying type is Edm.String, but has this annotation applied, as well as a validation that the syntax matches the ISO 8601 DateTime syntax.

This works well since any date/time types are conveyed as strings in JSON anyway.

In our stack, we would then support the CLR DateTime datatype, advertise it in $metadata either as a string with this annotation, or as the TypeDefinition. Existing clients would still be able to read this as a string, and clients that understood the annotation would be able to read the result and understand it as a DateTime value. In our client libraries we would add support for understanding this annotation when generating strongly typed classes and materialize the result using the DateTime datatype.

Given that we have such an engaged group of interested parties, I wanted to reach out to this group to see if that would address your real-world scenarios?

mikepizzo on 14 Jun 2018

@mikepizzo - This is a very good idea. It will require clients to create custom code to properly understand it, but it is workable.

It keeps with the goal of OData to discourage the use of DateTime, while allowing a way forward for those of us who have to use it. (I still maintain that OData should not be in the business of choosing the good datatypes from the bad, but rather creating a protocol that can connect REST services to clients with as little friction as possible.)

But I think this is a great compromise that will get both groups what they are looking for!

I really appreciate you keeping on this issue and working to find a way for us to use the awesomeness that is OData.

I have one worry, will the underlying data type is String allow for date filtering operations? (I would guess not, as it would be just a string...)

Vaccano on 14 Jun 2018

@mikepizzo Assuming that they add back the "edm.DataTime" (was in OData V3) into the metadata generated from the odata service, this would be great.

This is also great since the models on the OData service don't have to change if the developer left them as DateTime properties.
BUT it will cause issues since any dev using the DateTime property in their models currently get that property mapped to DateTimeOffset in the metadata...
Maybe add an option that the dev can set in the WebApiConfig?
config.EnableDateTime or something like that and then default to FALSE so it does not break all current implementations.
Then this flag can be used to generate the metadata. If TRUE the metadate would have "edm.DateTimeOffset" else "edm.DateTime" any where the model had properties of "DateTime".
If the model already is of type "DateTimeOffset" then ignore the flag.
There is code already in OData classes that is turning DateTime properties into DateTimeOffset metadata so this should be easy to wrap with the "EnableDateTime" flag.
Just a suggestion...

This would require little change at the client side for users currently using OData controls from Telerik or DevExpress with Winform and ASP.NET WebForm clients, since those controls still have lingering code from the V3 standard.
Unfortunately, any client that generated the proxy from the OData Client code generator would have to update their generated file on the clients AND the OData Client code generator would need tested but I think it still has code for generating based on "Edm.DateTime".

Hate that I posted this issue back in 2014 and it is just now getting attention but some "love" is better than "no love". 👍

I have an environment that includes both OData V3 and OData V4 in production and would be happy to test any changes to the OData libraries.

ImGonaRot on 14 Jun 2018

@mikepizzo thanks for the persistence in this ... much appreciated.

consider this object definition ...

public class TestObject
{
    // a point in time (should be offset ... I agree with the standard here)
    public DateTimeOffset CreatedDate { get; set; }

    // represents an offset from a given date within a day to point to a time on a clock
    public TimeSpan TimeOfDay { get; set; }    

    // I see this all the time, i receive these and have to deal with them, 
    // I don't know the client offset, all i can do is store this as is I assume the "offset" is "whatever the source systems offset is" and have no choice but to store only what i have as UTC (best practice)
    public DateTime TransactionDate{ get; set; }

    // now here's what I think you are proposing for the above property situation
    [Date]
    public string TransactionDate { get; set; }
}

... that raises lots of questions for me ...
If DateTime is hated so much why not remove the obscurity altogether and do this ...

public class TestObject2 
{
       // hold only year month day and supports via OData "date operations"
       // currently doesn't exist, could be defined in OData framework and mappable to either DateTime / DateTimeOffset using a rule defined by the developer
       public Date ADate { get; set; }  

       // holds only time of day (or an amount of time)
       public TimeSpan TimeAmount { get; set; }

      // holds the time zone information
      public TimeSpan Offset { get; set; }
}

In short ...
I think the best solution is to allow for WebAPI OData to "configure the assumption to be made" for a DateTime value, but "prefer" using the latter property types in TestObject2 with a means to take an ISO standard string for a Date only or Time only as needed.

I think the latter should be written in as "the standard" and the former with the assumption "supported" for legacy reasons but documented as "deprecated" to encourage developers to more to the new standard but at least give them the option where it's not possible.

TehWardy on 15 Jun 2018

@TehWardy Unfortunately TestObject2 does not work well with grid controls and filtering.
On the client side, devs would have to "merge" the two columns "ADate + TimeAmount" into one column (assuming they want one column containing date + time) and then intercept the filter events on the grid controls to query the OData service based on the data in the "merged" column.
It gets even more complicated when using "strings" as the datetime because this "01/01/2018 01:04" is not the same as "1/1/2018 1:04" and using compares like > or < trip this up unless the dev adds string to date formatters on the grid controls but then again we are back to intercepting filter events on the grids.

This would make the binding of grid controls more complicated than they should be.
OData V3 and grid binding in both DevExpress and Telerik just work out of box.

ImGonaRot on 15 Jun 2018

@ImGonaRot I disagree ... if you are combining the two fields then either use DateTime (which lacks precision and therefore results in assumptions ... or use DateTimeOffset which is the correct way to determine a point on calendar which is the binding scenario you are describing and works perfectly fine today (i have examples of exactly this scenario working perfectly with Kendo UI in business applications already).

Collection of new data should only be done to the latest standard and if you refuse to follow the standards for new data collection scenarios I think it's fair to any framework to fight you with that.

For old data scenarios / scenarios where you don't control the other end though I believe what I have suggested works fine as I still allow for supporting DateTime or a means to "map" a single string ina DTO on the endpoint to multiple fields or a DateTimeOffset as needed even without DateTime support but I have clearly stated that I as API builder would allow the receiving of DateTime values even if it wasn't part of the OData standard to allow for businesses that refuse to join the latest standards or scenarios where doing so is reasonably possible.

A note worth adding here ...
Any half decent server stack for OData would allow for OData endpoints that map to entities (which OData already supports) ... so even if you choose to store a DateTime value in your database there's no reason why you can't receive it as TestObject2 then map anyway.

In a situation where you can't reasonably do that I would question the value of using OData at all as you likely want to do that how I handle data imports for systems like SAP (which seems to refuse to adhere to any form standards and even goes as far as making up its own standards for XML which make no logical sense) ... which is to receive a raw blob of XML as a string and then parse it and map to an entity behind the API layer.

I think to be fair to @mikepizzo and the OData team here, having a goal that pushes people forward instead of holding people back or encouraging that is a good call as long as allowances are made that mean we can handle the odd scenario.

One possibility is to look at attribute based mapping in the object sent to the API something like this ...

public class FooController : ODataController<Foo>
{
       static Func<Foo> customParser = (request) => { return new Foo(request.Content.ReadAsString()); }

       [DeserializeWith(c => customParser)]
       public IHttpActionResult Post([FromBody]T aFoo)
       {
             ....
       }
}

something like this reduces the complexity of building out "custom serializers and deserializers" but also allows for virtually any data to be received as any kind of object.

A similar logic pattern could also be applied to get requests too.
I currently feel that mapping to and from a request stream is way too complex and part of the reason for that may be something to do with the fact that OData is not just about API's that receive data in 1 format.

I challenge you @ImGonaRot to find a scenario that couldn't be handled by this post and my previous combined, or some variation of.

TehWardy on 17 Jun 2018

@TehWardy Like @robertmclaws, you are assuming devs are using HTML5 controls like Kendo with JavaScript libraries like Moment.js.
I am a dev that lives in the "past" with WinForms, WPF, and ASP.NET WebForms.
I currently still develop and maintain apps in this "legacy" world and I'm 99.9% sure I will remain in that "legacy" world for 10 more years. Anyway...
As such, I still use MS Grids, Telerik Grids, and DevExpress Grids to help my development time on projects go faster and I can tell you that those companies don't support DateTimeOffset with "any" of their WinForm / WebForm controls out of box.
It is sad that very large 3rd party companies like Telerik and DevExpress are not updating their controls to support DateTimeOffset but until they do, I am stuck with large modifications to my code base to "work around" their DateTimeOffset issues when using OData V4.
I would love for the companies that employ me to move into the "new" world you speak of where everyone is using DateTimeOffset (which is a Microsoft standard) but unfortunately that will not happen any time soon.

My point is that OData V3 worked perfect with DateTime yet in OData V4 they removed it.
I would like OData V4 and beyond to support BOTH DateTime and DateTimeOffset.
I'm not sure why we can't have both.
MS SQL supports both and so does the MS .NET Framework.
I currently use REST OData V3 and some WCF as the business tiers without any problems but OData V4 does not work well for our companies as a REST web service without breaking the current web contracts for DateTime.

If MS does not want to add it back that is fine, our current OData V3 and WCF is working fine and will for many years to come.

I only opened the ticket to see if they could put it back.

ImGonaRot on 18 Jun 2018

I think if OData is not going to support "DateTime" then they should throw an exception when the dev tries to create a class with DateTime properties RATHER than automatically converting them into DateTimeOffset in the metadata and the serializer / deserializer.

So this should FAIL if the OData team does not want to support DateTime

public class TestObject
{
    public Datetime CreatedDate { get; set; }
}

This way any proxy generators would also fail and it would be clear to the service consumer that DateTime is not supported.

ImGonaRot on 18 Jun 2018

@ImGonaRot I use telerik controls both on the web and for desktop clients.
I have code running in WPF and winforms apps ... the lack of support for DateTime is not an issue if you know what to do.

I have OData V4 working on top of an EF model that exposes a legacy database with DateTime values bound to UI in both desktop and web components.

At this point i'm actually confused as to what your problem really is?
My issue boils down to implicit accuracy ...

Binding works fine since the API always serves up "strings" of serialized data anyway ... worst case you have a single block of custom serialization and a single block deserialization code to write and you're done.

Could you show us an example of something that won't work unless OData supports DateTime values?

TehWardy on 18 Jun 2018

@TehWardy I never said it doesn't work. I said it is more difficult than it was in OData V3.
Yes I can bind grids in WinForms, WebForms, and WPF to OData V4 datasources BUT with OData V3 and WCF, I don't have to make any code changes on the client to get things like filtering, sorting, editing to work once they are bound to an OData V3 datasource.
When binding to OData V4 I have to edit the filtering events, sorting events, and editors so they "understand" DateTimeOffset, else they will fail.
Both Telerik and DevExpress Date editors bound inside a grid don't understand DateTimeOffset.

If the OData team decides to choose String for DateTime then there will be other issue such as querying.
Since date string "01/01/2018 04:33" is not the same as date string "1/1/2018 4:33" and if you try do a "BETWEEN" you will find that the two will not filter correctly since they are of a different sort order in a string list.

Here is a link for DateTimeOffset with DevExpress WebForms
Quote "Our ASPxDateEdit editor doesn't support the DateTimeOffset type out of the box. "
https://github.com/DevExpress-Examples/aspxgridview-how-to-edit-a-datetimeoffset-column-t584661

And Telerik
Quote "Adding support for DateTimeOffset is not currently planned"
https://www.telerik.com/forums/datetimeoffset-column-filtering-and-sorting

Please let me know if you want source code and I can drum up a sample project.
But to be clear, I am already using both V3 and V4 in production on both Win and Web forms and for V3 I made no client modifications but for V4 I have made lots of modifications and can post those here if needed.

ImGonaRot on 18 Jun 2018

Ah I c ... I use telerik stuff but only the JS based pieces with my web apps.
Doing so means I can take the raw DateTimeOffset string and treat it like a DateTime leveraging the fact that the browser supports both types and treats them equally as a "new Date" when given a string representation in a known format.

In short ... your problem is caused by strong typing in .Net languages and your choice to use the server side frameworks.

I wrote a convention based JS wrapper around Kendo UI's datasource object that can construct a datasource for any Grid (or other control that hangs off an odata-v4 endpoint).
The wrapper implements the parse function once and I call that wrapper all over my code so I had only one place to solve the problem.

Whilst agree it's a pain in the ass in some situations the code is actually dead simple if you model your framework around kendo right.
To get it working though I had to extend OData too.
I'll throw a gist together so that people like @mikepizzo can see too (if you guys think there's some value to it).

TehWardy on 19 Jun 2018

EF Core 2.1's new ValueConverter functionality offers a potential workaround for those looking to query on SQL datetime columns. The problem here is that regardless of the CLR type on the entity, OData converts date/time literals to the DateTimeOffset CLR type in all cases. This type then serializes to SQL with too much precision, resulting in a cast exception being returned from SQL Server (it simply has too many milliseconds).

We explored adding our own IUriLiteralParser to the Microsoft.OData.UriParser.CustomUriLiteralParsers class, but Microsoft.AspNet.OData.Query.Expressions.FilterBinder (and related helpers) are very strict about types (which is good, if only they were more extensible). We made it through two reflection hacks before deciding the only way to accomplish this is a fork, which would likely take at least a couple days to get to a "working for my use case" state. To be clear, we eventually want to convert everything to datetime2/datetimeoffset, but constraints imposed by legacy systems make the unfeasible presently.

EF Core 2.1 introduced the ValueConverter which, in addition to converting values in the entity to and from the persistence store, also inspects the LINQ abstract syntax tree and performs type conversions on constants/literals. So OData transforms the date/time literal in the query string "$filter=orderDate ge 2015-01-01T00:00:00.000Z" to a DateTimeOffset, and then our ValueConverter will transform it back to a DateTime.

You must use DateTimeOffset on your entity (your SQL datatype can still be datetime):
public DateTimeOffset OrderDate { get; set; }

Create a ValueConverter:
internal static ValueConverter<DateTimeOffset, DateTime> DateTimeToDateTimeOffset = new ValueConverter<DateTimeOffset, DateTime>(model => model.DateTime, store => DateTime.SpecifyKind(store, DateTimeKind.Local));

Map it:
entityTypeBuilder.Property(x => x.Entered).HasConversion(ValueConverters.DateTimeToDateTimeOffset);

Caveats:

You'll need to modify the ValueConverter to handle timezones in a way that is appropriate for your use case
You CANNOT disable query parameterization as EF will no longer perform the type/value conversion. This is enabled by default and there aren't many reasons to turn it off.
[EnableQuery(EnableConstantParameterization = false)] // This will break our hack!
If you can use datetime2/datetimeoffset, do so and avoid this hack

From our exploration, we don't see any reason why the OData team couldn't add full support for DateTime. I realize this would be a non-standard feature outside of the OData V4 spec, but historically Microsoft hasn't shied away from adding their own extensions to public specifications :-). With so many projects working to modernize legacy systems using an agile, incremental approach, this is critical functionality, and the workaround above has limits..

chris-clark on 28 Aug 2018

👍2

@ImGonaRot code templating or generics might help you there ... fix it once, re-apply the same thing everywhere ... works fine for my js framework.

That said ... i'm used to working with telerik stuff which is basically a framework of such hacks ... after the first MB of crap work around code you give up trying to do things perfectly and just build hooks that let you override every last aspect of the poor underlying framework you're sat on ... in that respect the implementation we have of OData is much the same ... a hack here, a tweak there.

TehWardy on 28 Aug 2018

@chris-clark at one point i considered writing my own OData framework just using raw WebAPI and the ODataLib library to do the uri params to LINQ handling.

I actually genuinely don't understand why this is so complex as a problem until i notice that Microsoft wants you to pull in their full rest-tier and then it all clicks ... the problems we face just using the basics are because we choose not to take a dependency on 100 other things from nuget.

How dare we!
It really bugs me when i can't just take what i need and get forced to take lots of other stuff.

Feels like MS is keen to become SUN and certain teams want to build C# libs like Java ... WHY!!!

TehWardy on 28 Aug 2018

I just wanted to update the thread on the original issue:

We discussed this at length at our September Face-to-Face OASIS OData Technical Committee meeting, and ultimately agreed to add a formal "Core.LocalDateTime" typedefinition to the vocabulary. The semantics of this typedefinition is that it is a string that represents a date-time value with no offset, and the format of the string is enforced through a matches validation. In addition, we stated that the cast function must accept such a string (lacking an offset) to cast to a DateTimeOffset (as UTC) in order to support use of the values in datetime expressions.

See https://issues.oasis-open.org/browse/ODATA-1229 for details.

We are in the process of implementing support for this new TypeDefinition in ODataLibrary and WebAPI OData. While there is still a strong preference to represent datetime values with timezone where-ever possible, hopefully this will address some of the very valid concerns raised in this thread.

mikepizzo on 1 Nov 2018

👍1

@mikepizzo This is great news but if it is a string, how will this convert back into SQL queries when using filters?

Here is what I posted a while back.

If the OData team decides to choose String for DateTime then there will be other issue such as querying.
Since date string "01/01/2018 04:33" is not the same as date string "1/1/2018 4:33" and if you try do a "BETWEEN" you will find that the two will not filter correctly since they are of a different sort order in a string list.

I look forward to seeing / testing the implementation / source code.
Thanks for looking into this issue.

ImGonaRot on 1 Nov 2018

@ImGonaRot Still to be designed, but in WebAPI we would probably know that the underlying value was a CLR DateTime value, so we would build the correct LINQ expression to be passed down to the underlying provider. Very eager to get feedback on this feature...

mikepizzo on 1 Nov 2018

I am updating a NET 4.7 project to latest OData libraries. I need to have Date value support on the OData queries within the $filter option (i.e. $filter=BirthDate eq 1900-01-01). I was able to provide this support by creating an action filter that converts my Date values into DateTimeOffset string values before the OData request is handled by the OData framework.

Here is a link to my solution
https://github.com/kbarkhausen/OData.ActionFilter.AddDateTimeSupport

Welcome any comments, suggestions or ideas on how to improve this support. You can easily customize this filter to support any DateTime value (i.e. $filter=BirthDate eq '03/14/2018')

kbarkhausen on 29 Mar 2019

Webapi: OData V4 service should support DateTime

Work Item Details

Most helpful comment

All 78 comments

Related issues