Opened 8 years ago

Last modified 4 years ago

#1517 reopened defect

'DateTime' not handled by v.in.ogr

Reported by: grasslandtom Owned by: grass-dev@…
Priority: normal Milestone: 6.4.6
Component: Database Version: 6.4.2 RCs
Keywords: v.in.ogr, DateTime, TIMESTAMP Cc:
CPU: Unspecified Platform: Unspecified

Description

steps to reproduce:

_ create a table in PostGIS-database 'test':

create table test (id INT, datetime TIMESTAMP);
SELECT addgeometrycolumn('test', 'the_geom', 4030, 'POINT', 2);
insert into test values (1, '2001-01-01 00:12:00', st_setsrid(ST_point(12, 50), 4030));

_ import in GRASS:

v.in.ogr dsn='PG:dbname=test' out=test layer='test(the_geom)' location=new_test

This raises the error

DBMI-DBF driver error:
SQL parser error: syntax error, unexpected NAME processing 'datetime'
in statement:
create table test (cat integer, datetime datetime, id integer)
Error in db_execute_immediate()

When the location is connected to PostGIS:

_ import in GRASS

v.in.ogr -o dsn='PG:dbname=test' out=test_out layer='test(the_geom)'

raises the error

DBMI-Postgres driver error:
Cannot execute: 
create table test_out (cat integer, datetime datetime, id integer)
FEHLER:  Typ »datetime« existiert nicht
LINE 1: create table test_out (cat integer, datetime datetime, id in...
                                                     ^

(Sorry for the German error message. This one seems to come from the PostgreSQL Server which was running before I set the respective environment variables)

When the location is connected to SQLite, the import works without a warning respective to DateTime? but

db.columns test_out

raises

WARNING: SQLite driver: unable to parse decltype: datetime
WARNING: SQLite driver: unable to parse decltype: datetime
WARNING: SQLite driver: column 'datetime', SQLite type 3 is not supported
cat
id

Note that

ogrinfo 'PG:dbname=test' 'test(the_geom)'

gives in the last lines

datetime: DateTime (0.0)
id: Integer (0.0)
OGRFeature(test(the_geom)):0
  datetime (DateTime) = 2001/01/01  0:12:00
  id (Integer) = 1
  POINT (12 50)

Thus, DateTime? seems to exist in OGR. Though the SQL data type is TIMESTAMP.

Change History (3)

comment:1 in reply to:  description ; Changed 8 years ago by neteler

Resolution: invalid
Status: newclosed

Replying to grasslandtom: ...

This raises the error

DBMI-DBF driver error:
SQL parser error: syntax error, unexpected NAME processing 'datetime'
in statement:
create table test (cat integer, datetime datetime, id integer)
Error in db_execute_immediate()

You cannot use a reserved SQL word as column name. 'datetime' is reserved...

comment:2 in reply to:  1 Changed 8 years ago by grasslandtom

Resolution: invalid
Status: closedreopened

Replying to neteler:

Replying to grasslandtom: ...

This raises the error

DBMI-DBF driver error:
SQL parser error: syntax error, unexpected NAME processing 'datetime'
in statement:
create table test (cat integer, datetime datetime, id integer)
Error in db_execute_immediate()

You cannot use a reserved SQL word as column name. 'datetime' is reserved...

Obviously not for PostgreSQL. Otherwise the first line in the first code block could not work. As a reference:

http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html

Further in PostgreSQL:

alter table test rename datetime to something;

then in GRASS:

v.in.ogr dsn='PG:dbname=gs_gk_workspace' out=test layer='test(the_geom)' location=new_test

raises

DBMI-DBF driver error:
SQL parser error: syntax error, unexpected NAME processing 'datetime'
in statement:
create table test (cat integer, something datetime, id integer)
Error in db_execute_immediate()

Here it's obviously not the column name, but the data type, that causes trouble.

comment:3 Changed 4 years ago by neteler

Milestone: 6.4.26.4.6
Note: See TracTickets for help on using tickets.