MySQL Bugs: #52550: DateTimeOffset row cannot be used as select condition

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	CPU Architecture:	Any

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.

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.

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.

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?

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

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.

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.

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.