Timestamps in Gnucash Mysql

[Phil Longstaff]

On August 12, 2009 08:25:31 am Phil Longstaff wrote:
> On August 12, 2009 03:06:40 am marcus.wolschon at 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