Bug #52550 DateTimeOffset row cannot be used as select condition
Submitted: 2 Apr 2010 15:22 Modified: 13 Aug 2010 10:40
Reporter: Marcos Boyington Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.1 OS:Any
Assigned to: Reggie Burnett
Triage: D4 (Minor)

[2 Apr 2010 15:22] Marcos Boyington
Description:
I am unable to use a timestamp column in a select statement.

timestamp columns are converted to a C# DateTimeOffset (trying to use a DateTime causes an error).

When trying to do something like:

from p in db.Products where p.PostedDate>=DateTime.Now select p;

(where p.PostedDate is a DateTimeOffset)

I get the following exception:

MySqlException occured
Unable to serialize date/time value

How to repeat:
Create a table with a timestamp row, and use entity framework to select with a condition on the timestamp row

Suggested fix:
Either allow timestamp to convert to DateTime, or allow DateTimeOffset rows to be used in a select statement.
[6 Apr 2010 7:19] Tonci Grgin
Hi Marcos and thanks for your report.

Judging from ProviderManifest.xml, DateTimeOffset is a primitive type of timestamp and TimeStampAdd & TimeStampDiff are on to-do list so I think c/NET devs are aware of this problem but I'll consult to be sure.

Now, DateTime.NOW is of Type DATETIME so you might wish to retest with p.PostedDate as a DateTime and see if it works.
[7 Apr 2010 3:38] Marcos Boyington
Thanks Tonci.  I had gone in and looked at ProviderManifest.xml myself earlier today, and discovered that what I should've been using datetime (not timestamp).
For some strange reason, I was not aware that MySQL had a datetime, and had been using timestamp as date&time combination.  Live and learn! Thanks again.
[7 Apr 2010 7:33] Tonci Grgin
Marcos, I am glad your code works but I will still mark the report as verified because:
  o We have things on to-do list
  o I have reservations about consistency of some changes
  o There might be some things to document here

Reggie?
[11 Aug 2010 20:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115533
[11 Aug 2010 20:33] Reggie Burnett
We didn't change this with 6.0, 6.1, or 6.2 as they are GA.  You can get the DateTime portion of the column with the .DateTime property.  So the query above should be written as:

from p in db.Products where p.PostedDate.DateTime >= DateTime.Now select p;

We did change this in 6.3 though.  Starting with 6.3.4, all timestamp columns will be surfaced as DateTime.  We thought this made more sense.
[13 Aug 2010 10:40] Tony Bedford
An entry has been added to the 6.3.4 changelog:

When attempting to carry out an operation such as:

from p in db.Products where p.PostedDate>=DateTime.Now select p;

Where p.PostedDate is a DateTimeOffset, and the underlying column type is a TIMESTAMP, the following exception was generated:

MySqlException occurred
Unable to serialize date/time value

MySQL Connector/NET has now been changed so that all TIMESTAMP columns will be surfaced as DateTime.
[20 Sep 2010 14:03] Andrew M
I have .net connector v  6.3.4

When I try to insert an entry that has auto-generated time-stamp filed, I get an exception. Could not cast DateTime to DateTimeOffset.

As I understand since 6.3.4 the mapped property type should be DateTime, but it fails on validation.