[Mono-list] Mono.Data.SqliteClient always report text as DataTypeName

[Aaron Bockover]

Dates in Sqlite are stored as strings, in the format "YYYY-MM-DD
HH:MM:SS". If you read them in and use some variation of
System.DateTime.Parse() to convert the string to a System.DateTime, the
conversion may be incorrect if the current locale does not support this
format. If you must parse it, be sure to manually specify the US to the
Parse method. 

However... System.DateTime.Parse is extremely inefficient, and I
recommend avoiding it altogether. Instead, store a unix expoch timestamp
in the database, and use
Mono.Unix.Native.NativeConvert.ToDateTime/FromDateTime to do the
conversions to/from. 

If you must store it as a formatted date string in the database, you can
also use this SQL to do the conversion to epoch time:

SELECT strftime("%s", "2005-12-14 10:32:55");

This will return the time in UTC. If you need it for the current
timezone:

SELECT strftime("%s", "2005-12-14 10:32:55", "localtime");

I've never tested this method in practice, but I would imagine it's much
faster than using System.DateTime.Parse() on the string variant.

To give you a rough idea of how slow System.DateTime.Parse is:

In Banshee, the Tracks table had two "DATETIME" columns. There were 4000
rows in the table. Loading all 4000 rows meant System.DateTime.Parse()
was called 8000 times. This load operation took about 20 seconds. When I
changed the value being stored from a formatted date/time string to an
epoch int, using ToDateTime to do the conversion took only about 1.5
seconds to load all 4000 rows.

--Aaron


On Wed, 2005-12-14 at 08:05 -0600, Duncan McQueen wrote:
> Will this error be related to DateTime values not being reported correctly?
> 
> On 12/14/05, Julien Sobrier <julien at sobrier.net> wrote:
> > Aaron Bockover wrote:
> > > Internally in sqlite, everything is stored as a string, regardless of
> > > what type a column was assigned during table creation.
> > >
> > > The only way to know the "type" is to parse the table definition from
> > > the sqlite_master table. This should probably be done for convenience
> > > inside Mono.Data.SqliteClient.
> > >
> > > For instance, run this query:
> > >
> > > SELECT sql FROM sqlite_master WHERE name="Tracks";
> > >
> > > That will return the SQL used to create the "Tracks" table. You can then
> > > parse that result to build a map of column names->types.
> > >
> > > There is some example code for this in Banshee:
> > > http://cvs.gnome.org/viewcvs/*checkout*/banshee/src/Database.cs
> > >
> > > --Aaron
> >
> > Thanks a lot, I'll look at it.
> >
> > _______________________________________________
> > Mono-list maillist  -  Mono-list at lists.ximian.com
> > http://lists.ximian.com/mailman/listinfo/mono-list
> >
> _______________________________________________
> Mono-list maillist  -  Mono-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list