List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 6 2006 6:40pm
Subject:Re: DateTime limits
View as plain text  
Ben Clewett wrote:
<snip>
 > C# has two DateTime constants:
 >
 >     DateTime.MinValue = '0001-01-01 00:00:00.000'
 >     DateTime.MaxValue = '9999-12-31 23:59:59.999'
 >
<snip>
 >
 > MySQL really doesn't like these values, it shows warnings:
 >
 > +---------+------+-------------------------------------------------+
 > | Level   | Code | Message                                         |
 > +---------+------+-------------------------------------------------+
 > | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' |
 > +---------+------+-------------------------------------------------+
 >
 > The real problem with these warning is:
 >
 > 1. This date is legal, if a little unlikely.
 > 2. Any warning crash MySql.Data.dll!!!
<snip>

Ben Clewett wrote:
 > Hi Barry,
 >
 > This will happen when comparing against a TIMESTAMP field.
 >
 > CREATE TABLE a ( t TIMESTAMP );
 >
 > SELECT * FROM a WHERE t > '0001-01-01 00:00:00';
<snip>

Ben Clewett wrote:
> Hi Barry,
> 
>  > Well removing 'explicit' warnings for every user having problems with
>  > 3rd party modules would have mysql without any warnings nowadays ;)
>  >
>  > i think that your mono should get more stable.
> 
> I completely take this on board.  This is a bug outside MySQL.
> 
> Warnings are very useful.  When we test code and get a warning, this 
> almost always results in us re-coding.  Eg:
> 
> ... WHERE '2006-06-06' > '2006-06-06 12:00:00'
> 
> This will give a valid warning, this is bad SQL, time for a re-code!

This will not give a warning, as it is perfectly valid sql.  (It will simply 
return no rows, as the WHERE clause condition cannot be satisfied.)  Why do you 
think otherwise?

> BUT my warning is a special case:
>   - It's not bad SQL.

Yes, it is bad sql.  Your WHERE clause is

   WHERE t > '0001-01-01 00:00:00'

As t is a TIMESTAMP column, mysql must convert the DATETIME constant to a 
TIMESTAMP, but '0001-01-01 00:00:00' is not a valid TIMESTAMP.  Hence the warning.

>   - Because of the C# DateTime object, this happens often.
> 
> Therefore a candidate for dropping.  Which will also avoid bugs in badly 
> written MySQL clients :)
> 
> But, I've probably said enough on the point :)
> 
> Ben

According to the manual 
<http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html>, the 
officially supported range for DATETIME columns is '1000-01-01 00:00:00' to 
'9999-12-31 23:59:59', though '0001-01-01 00:00:00' seems to work in practice.

 From the same manual page, the officially supported range for TIMESTAMP columns 
is '1970-01-01 00:00:00' to partway through the year 2037.  In practice, 
TIMESTAMP columns take timezone into account.  Hence, as my offset from GMT is 
-5, the earliest TIMESTAMP I can get away with is '1969-12-31 19:00:01'.  That is,

   SELECT * FROM a WHERE t > '1969-12-31 19:00:00';

produes a warning, but

   SELECT * FROM a WHERE t > '1969-12-31 19:00:01';

does not.

I believe your problem begins with your assumption that DATETIME and TIMESTAMP 
are interchangeable.  They are not.  You would probably be fine using C#'s

   DateTime.MinValue = '0001-01-01 00:00:00.000'

for actual DATETIME columns, but it just isn't valid for TIMESTAMP columns. 
That said, mysql manages to do the right thing when faced with this query.  As 
'0001-01-01 00:00:00.000' is earlier than the first valid TIMESTAMP, it is 
converted to a TIMESTAMP of 0, one second before the earliest valid TIMESTAMP, 
and the query proceeds, giving the results you expected, I believe.  It also 
issues a warning to let you know what it did.

This is where the second problem comes into play.  Your system crashes on 
warnings!  As you've already admitted, this is "a bug in the .NET MySQL 
library".  The solution seems plain.  The library must be fixed to handle 
warnings.  Mysql should not be changed back to the old days of silently changing 
your query without issuing warnings.

Michael






Thread
DateTime limitsBen Clewett6 Jun
  • Re: DateTime limitsBarry6 Jun
    • Re: DateTime limitsBen Clewett6 Jun
      • Re: DateTime limitsBarry6 Jun
        • Re: DateTime limitscknipe6 Jun
          • Re: DateTime limitsDuncan Hill6 Jun
            • Re: DateTime limitsJamesDR6 Jun
        • Re: DateTime limitsBen Clewett6 Jun
          • Re: DateTime limitsBarry6 Jun
            • Re: DateTime limitsBen Clewett6 Jun
              • Re: DateTime limitsMichael Stassen6 Jun
                • Re: DateTime limitsBen Clewett7 Jun
          • Re: DateTime limitsChris W6 Jun