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

GDAL · August 22, 2009, 10:36pm

#730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
----------------------+-----------------------------------------------------
Reporter: neteler | Owner: grass-dev@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>

neteler · August 27, 2009, 5:58pm

Hi,

On Sun, Aug 23, 2009 at 12:36 AM, GRASS GIS<trac@osgeo.org> wrote:

#730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
----------------------+-----------------------------------------------------
Reporter: neteler | Owner: grass-dev@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

GDAL · September 19, 2011, 8:16am

#730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
----------------------+-----------------------------------------------------
Reporter: neteler | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone: 6.4.0
Component: Database | Version: 6.4.0 RCs
Keywords: | Platform: All
Cpu: All |
----------------------+-----------------------------------------------------

Comment(by mmetz):

Replying to [ticket:730 neteler]:
> There are some troubles with datetime columns in SQLite:
>
[snip]
>
> Not sure what to do: map to which DB_SQL_TYPE_xxx?
>
The SQLite documentation says that datetime is of the format "YYYY-MM-DD
HH:MM:SS". According to lib/db/dbmi_base/datetime.c, DB_SQL_TYPE_TIMESTAMP
seems to be an exact match. Try trunk r48359.

Markus M

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

GDAL · May 1, 2013, 11:49pm

#730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
------------------------------+---------------------------------------------
Reporter: neteler | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone: 6.4.0
Component: Database | Version: 6.4.0 RCs
Keywords: SQLite, datetime | Platform: All
Cpu: All |
------------------------------+---------------------------------------------
Changes (by hamish):

* keywords: => SQLite, datetime

Comment:

status?

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

GDAL · May 2, 2013, 6:57am

#730: sqlite: troubles with datetime columns: unable to parse decltype: datetime
-----------------------+----------------------------------------------------
  Reporter: neteler | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone: 6.4.0
Component: Database | Version: 6.4.0 RCs
Resolution: fixed | Keywords: SQLite, datetime
  Platform: All | Cpu: All
-----------------------+----------------------------------------------------
Changes (by neteler):

* status: new => closed
* resolution: => fixed

Comment:

Works (testing with same file as reported 4 years ago):

{{{

GRASS 6.4.3svn (patUTM32):~ > db.select meteo_iasma_hourly_modis_overpass
cat|station|data|t_max|t_min|t_mean
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 01:00:00|-1.8|-2.5|-2.2
...

GRASS 6.4.3svn (patUTM32):~ > db.connect -p
driver:sqlite
database:$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
schema:
group:
}}}

Closing.

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