Actually, looking over the v2 data types: http://sqlite.org/datatypes.html
I see "TIMESTAMP." I haven't tested it, but it's probably just a 64 bit integer. However, if a column is of that type, it would be nice to automatically convert to a System.DateTime using the appropriate NativeConvert methods. Just a thought. --Aaron On Sun, 2006-01-22 at 18:41 -0500, Aaron Bockover wrote: > First of all, this says a lot (I just mentioned it in another reply): > http://sqlite.org/datatype3.html > > On Sun, 2006-01-22 at 18:22 -0500, Joshua Tauberer wrote: > > Aaron Bockover wrote: > > > Wow. I'm not sure why this change was made, but in > > > Mono.Data.SqliteClient/SqliteDataReader.cs, this happens now for columns > > > declared as INT/INTEGER: > > > > There was some confusion, I think either in bugzilla or on the mail > > lists, about DATETIME columns returning strings or something, so I > > figured that if you've declared a column as an INTEGER or DATETIME, > > that's probably the type of data you're going to be putting into it. > > Except, I realize now that there's no BIGINT equivalent in Sqlite. > > > > It was sort of a compromise between doing what one would expect of any > > data adapter versus doing exactly what Sqlite does. > > There should be no compromise here. If sqlite3 can store 8 bytes in an > INTEGER type, the bindings *must* account for it. Either always return > an Int64 or do some simple detection to see if the returned integer can > fit in an Int32, and then cast and return as such. > > > So... I'll undo that. Do you think I should also undo string conversion > > to DateTime for DATETIME columns? > > This is a little tricky. I had some experience with this in October, and > we did some profiling on it. The problem is that DateTime.Parse (and > other various similar methods) is extremely slow. I think there were > something like 60 string allocations per invocation of that method. I > was using DATETIME in Banshee and parsing when pulling data from the > reader. Each row had two DATETIME columns, and on a 4k database, it took > about 20 seconds to read. The bottleneck was DateTime.Parse of course. > > That being said, sqlite does store DATETIME values as strings, so there > are two options: > > a) Parse the date in a more efficient manner, keeping in mind that in > sqlite, it will always be in the same format (YYYY-MM-DD HH:MM:SS) > (DateTime.Parse "guesses" at various formats). > > b) Do some hackery like this (not sure how you would do this in the > reader, since it needs to be done at the statement level, before > committing): > > SELECT strftime("%s", ColumnNameThatIsADATETIME); > > The internal sqlite strftime function will convert the string to a unix > timestamp, normalized to UTC. You can pass "localtime" for example, as a > third argument to strftime if you don't want it in UTC. > > I would go with option (a). That would keep compatibility with databases > that use DATETIME and would expect a System.DateTime in return. Just > avoid DateTime.Parse. But do to the fact that internally the value is a > string, just using a DATETIME column in your table definition is > immediately less efficient than storing a timestamp. > > With all this in mind, here's probably the best way to handle dates in > sqlite: store them as INTEGERS as a unix timestamp and let the > application use Mono.Unix.Native.NativeConvert.ToDateTime/FromDateTime > to read/write the values. > > When I changed to doing this in Banshee, the load time went from 20 to > 1.5 seconds. > > I wrote about this last month too, if you want more details: > http://lists.ximian.com/pipermail/mono-list/2005-December/029900.html > > If you'd like I can take a look at the parsing issue and cook up a > patch, time permitting. It might also be nice to actually do detection > on INTEGER columns to see if it is appropriate to return an Int32. > > Cheers! > --Aaron > > > > _______________________________________________ > Mono-devel-list mailing list > Mono-devel-list@lists.ximian.com > http://lists.ximian.com/mailman/listinfo/mono-devel-list _______________________________________________ Mono-devel-list mailing list Mono-devel-list@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-devel-list