This project is read-only.
107

Closed

OData V4 service should support DateTime

description

OData V4 should support DateTime or you should give the developer a way of converting datatime to datetimeoffset when the service is called.
I have several serializable objects that are in 3rd party libraries (even Microsoft core classes) that have datatime properties that will not be exposable through OData V4 services.

here is OASIS meeting showing that Edm.DateTime was discussed as adding back but then was "defer this to a future version"?
https://issues.oasis-open.org/browse/ODATA-220
Closed Jan 15, 2015 at 2:08 AM by lianw
Closed here and moved the issue to GitHub (https://github.com/OData/WebApi/issues/136)

comments

GuilhermeM wrote Jul 23, 2014 at 4:50 AM

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.

cquick wrote Jul 23, 2014 at 2:41 PM

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.

TimMc wrote Jul 28, 2014 at 4:54 PM

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.

schaibaa wrote Jul 30, 2014 at 3:49 AM

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.

BullCreek wrote Jul 31, 2014 at 10:15 PM

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.

RoryPS wrote Aug 9, 2014 at 8:19 PM

+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.

Vaccano wrote Aug 11, 2014 at 10:46 PM

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.

MacOkieh wrote Aug 12, 2014 at 9:32 AM

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).

millman wrote Aug 19, 2014 at 2:46 PM

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!

gsulcer wrote Aug 25, 2014 at 3:49 PM

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".

Mikal wrote Sep 2, 2014 at 2:33 PM

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.

johncrim wrote Sep 3, 2014 at 12:55 AM

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.

johncrim wrote Sep 5, 2014 at 8:35 PM

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

johncrim wrote Sep 7, 2014 at 8:04 PM

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...

Kittoes wrote Sep 15, 2014 at 1:37 PM

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

jonagostinello wrote Oct 9, 2014 at 4:25 PM

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 

markrendle wrote Nov 17, 2014 at 1:29 PM

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.

jonagostinello wrote Nov 17, 2014 at 11:11 PM

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.

goroth wrote Nov 18, 2014 at 9:19 PM

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?

_kj_ wrote Nov 28, 2014 at 4:22 PM

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.

goroth wrote Dec 2, 2014 at 1:04 PM

goroth wrote Dec 2, 2014 at 1:05 PM

*** 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

DotNetWise wrote Dec 4, 2014 at 9:37 AM

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!

Xorcist77 wrote Jun 17, 2015 at 6:08 PM

I love what oData is meant to do, but it's been an uphill battle for us to adopt v4 in the practical sense. Removing DateTime instead of depreciating it doesn't give anyone time to make the adjustment and just outright breaks existing functionalities. Even with the release of 5.6, we're still required to use a date time offset format on the client-side, otherwise the server-side objects just don't get deserialized (resulting in a null object).

Please reinstate DateTime even if you leave it depreciated. We've been migrating from standard REST to oData, and DateTime was a bit part of the previous infrastructure done in Web API (and it just works). Moving to oData should be as simple as adding a new controller and changing the URI calls. We should be able to use our existing entities (which use DateTimes) and not worry about refactoring all the client-side code to use date time offset formats (but with no offset).