On August 12, 2009 08:25:31 am Phil Longstaff wrote: > On August 12, 2009 03:06:40 am marcus.wolsc...@googlemail.com wrote: > > I just found out that gnucash stores > > DatePosted and DateEntered as Strings > > instead of the proper DateTime or Timestamp > > sql data-types. > > > > Why is that? In XML it already uses a proper > > date-format. > > > > This makes is very hard to sort by date in > > SQL-queries and to select a range as you need > > a complex fulltext-index instead of a trivial > > integer index. (There are supposed to be MANY > > transactions and splits in there.) > > That goes back to the time when the sql backend couldn't support different > SQL/DDL code for the different db engines. There is no SQL standard for a > date/time that all 3 db's (sqlite3/mysql/pgsql) share. > > Now that the sql backend can support different column types for the 3 dbs, > it does make sense to use the proper types.
Also, sqlite does not have a datetime or timestamp but needs to use strings, and I've been coding mainly for sqlite, with mysql and pgsql taking 2nd place. Gnucash has both dates, and date-times. For dates, I can use CHAR(8) for sqlite, DATE for mysql and pgsql. For date-times, I can use CHAR(14) for sqlite, TIMESTAMP for mysql and TIMESTAMP for pgsql. Question: should the pgsql be "TIMESTAMP WITH TIME ZONE" or "TIMESTAMP WITHOUT TIME ZONE"? Phil _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
