[Mono-bugs] [Bug 77268][Wis] Changed - Sqlite DateTime Parameters Handled Incorrectly

[bugzilla-daemon at bugzilla.ximian.com]

Please do not reply to this email- if you want to comment on the bug, go to the
URL shown below and enter your comments there.

Changed by anmar at gmx.net.

http://bugzilla.ximian.com/show_bug.cgi?id=77268

--- shadow/77268	2006-03-08 08:44:42.000000000 -0500
+++ shadow/77268.tmp.22077	2006-07-04 07:02:32.000000000 -0400
@@ -146,6 +146,43 @@
 (automatically).  Also since Sqlite doesn't store DATETIMEs natively
 in any special format besides as a string, it makes some sense to me
 that the client user should specify how he wants the DateTime encoded
 in the DB.
 
 It's not an ideal situation, but I'm marking this as not a bug.
+
+------- Additional Comments From anmar at gmx.net  2006-07-04 07:02 -------
+Here I see several possible improvements for this situation:
+
+ 1. The default date representation in SqLite is 'The number of days
+since noon in Greenwich on November 24, 4714 B.C.'. Going from
+ToFileTime() to julian representation would be cheap enough. In this
+case the date functions in sqlite will parse the date properly by
+default without using modifiers.
+
+ 2. Push for a new date modifier in sqlite such as 'dotnet' or 'mono'
+that parses the ToFileTime() format. It's simple to implement, though
+I guess hard to get included into sqlite codebase. It also forces the
+use of that modifier when using dates in expressions. Sample
+implementation for parseModifier function:
+    case 'd': {
+      if( strcmp(z, "dotnet")==0 && p->validJD ){
+        p->rJD = (p->rJD/86400.0)/1000000000.0 + 2305813.5;
+        p->rJD += localtimeOffset(p);
+        clearYMD_HMS_TZ(p);
+        rc = 0;
+      }
+      break;
+    }
+
+ 3. Add a connection string option to select which way to store dates.
+Then it could use ISO 8601 format for those cases where we don't care
+about performance when parsing strings but prefer ease of use in sql
+expressions. The best about this options is that it would play well if
+you also access the same data through the odbc driver as it uses ISO
+8601 format.
+
+I'd prefer 1) as it's the simplest to work with, and can be even done
+in a somewhat backwards compatible way detecting the order of
+magnitude of the stored number.
+
+I'd be willing to provide patches for any of them.