[GRASS-dev] Re: [GRASS GIS] #730: sqlite: troubles with datetime columns: unable to parse decltype: datetime

[Markus Neteler]

Hi,

On Sun, Aug 23, 2009 at 12:36 AM, GRASS GIS<trac at osgeo.org> wrote:
> #730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
> ----------------------+-----------------------------------------------------
>  Reporter:  neteler   |       Owner:  grass-dev at lists.osgeo.org
>     Type:  defect    |      Status:  new
>  Priority:  normal    |   Milestone:  6.4.0
> Component:  Database  |     Version:  6.4.0 RCs
>  Keywords:            |    Platform:  All
>      Cpu:  All       |
> ----------------------+-----------------------------------------------------
>  There are some troubles with datetime columns in SQLite:
>
>  {{{
>  GRASS 6.4.0svn (patUTM32):> db.select meteo_iasma_hourly_modis_overpass
>  WARNING: SQLite driver: unable to parse decltype: datetime
>  WARNING: SQLite driver: unable to parse decltype: datetime
>  WARNING: SQLite driver: column 'data', SQLite type 3 is not supported
>  cat|station|t_max|t_min|t_mean
>  1|st14|8.3|7.1|7.7
>  2|st14|9.5|7.7|9.4
>  3|st14|-1.8|-2.5|-2.2
>  4|st14|-2|-2.4|-2.2
>  ...
>  }}}
>
>  The column is not shown at all above (but below). Indeed, for SQLite
>  itself the type exists and it doesn't have problems with the table:
>
>  {{{
>  sqlite3 $HOME/grassdata/patUTM32/meteo_iasma/sqlite.db
>  sqlite> .schema meteo_iasma_hourly_modis_overpass
>  CREATE TABLE meteo_iasma_hourly_modis_overpass (cat integer, station
>  varchar ( 255 ), data datetime, t_max double precision, t_min double
>  precision, t_mean double precision);
>  CREATE UNIQUE INDEX meteo_iasma_hourly_modis_overpass_cat on
>  meteo_iasma_hourly_modis_overpass ( cat );
>
>  sqlite> select * from meteo_iasma_hourly_modis_overpass;
>  1|st14|2001-11-01 10:00:00|8.3|7.1|7.7
>  2|st14|2001-11-01 11:00:00|9.5|7.7|9.4
>  3|st14|2003-04-11  1:00:00|-1.8|-2.5|-2.2
>  4|st14|2003-04-11  2:00:00|-2.0|-2.4|-2.2
>  ...
>  }}}
>
>  I see that in db/drivers/sqlite/describe.c there is no "datetime":
>
>  {{{
>    334      if (sscanf(buf, "decimal ( %d , %d )", length, length) == 2)
>    335          return DB_SQL_TYPE_DECIMAL;
>
>    336      if (sscanf(buf, "time ( %d )", length) == 1 ||
>    337          sscanf(buf, "timetz ( %d )", length) == 1)
>    338          return DB_SQL_TYPE_TIME;
>
>    339      if (sscanf(buf, "timestamp ( %d )", length) == 1 ||
>    340          sscanf(buf, "timestamptz ( %d )", length) == 1)
>    341          return DB_SQL_TYPE_TIMESTAMP;
>  }}}
>
>  Not sure what to do: map to which DB_SQL_TYPE_xxx?

Sorry to insist... any pointers?

Markus