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

[GRASS GIS]

#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?

 Markus

-- 
Ticket URL: <http://trac.osgeo.org/grass/ticket/730>
GRASS GIS <http://grass.osgeo.org>