Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

OCI Driver should convert date times with fractional seconds to int #11057

Closed
msmitherdc opened this issue Oct 20, 2024 · 3 comments · Fixed by #11058 or #11342
Closed

OCI Driver should convert date times with fractional seconds to int #11057

msmitherdc opened this issue Oct 20, 2024 · 3 comments · Fixed by #11058 or #11342
Assignees
@rouault

Comments

@msmitherdc
Copy link
Contributor

What is the bug?

When an OFTDateTime field is created, it is created as a TIMESTAMP

 else if (oField.GetType() == OFTDateTime)
    {
        snprintf(szFieldType, sizeof(szFieldType), "TIMESTAMP");
    }

As such, any DateTime field inserts, should convert the second (which are now a floating point) to an int to avoid ORA-01830: date format picture ends before converting entire input string

Steps to reproduce the issue

Insert a feature from geojson containing datetime into an oracle layer will error

Versions and provenance

3.9 and master

Additional context

No response
@jratike80
Copy link
Collaborator

Instead of dropping the fractional seconds, I think that GDAL could try to create the field with a proper parameter for fractional seconds https://www.oracletutorial.com/oracle-basics/oracle-timestamp/.
Unfortunately before knowing what precision to use GDAL should probably parse the whole GeoJSON data.

I also believe that for some users your test case "Insert a feature from geojson containing datetime into an oracle layer will error" might work depending on the value of the NLS_TIMESTAMP_FORMAT. Please add what NLS_TIMESTAMP_FORMAT value you have, and how many decimals do you have in the GeoJSON data.

@rouault rouault self-assigned this Oct 20, 2024
rouault added a commit to rouault/gdal that referenced this issue Oct 20, 2024
@rouault
OCI: use TIMESTAMP(3) and tweak NLS_TIME[STAMP][_TZ]_FORMAT to accept…
15365ad 
… milliseconds

Fixes OSGeo#11057
@rouault rouault mentioned this issue Oct 20, 2024
Merged
rouault added a commit that referenced this issue Oct 22, 2024
@rouault @github-actions
OCI: use TIMESTAMP(3) and tweak NLS_TIME[STAMP][_TZ]_FORMAT to accept…
6c6a348 
… milliseconds

Fixes #11057
@msmitherdc
Copy link
Contributor Author

msmitherdc commented Nov 23, 2024 •
edited
Loading

Just getting to build gdal oci plugin after this update and still getting the error.

ERROR 1: ORA-01830: date format picture ends before converting entire input string
 in OCIStmtExecute
ERROR 1: WriteArrowBatch() failed

Source data example (coming from geopackage 1.4):

  reporttime (DateTime) = 2024/11/21 14:00:00+00
  resulttime (DateTime) = 2024/11/09 12:38:28+00
  publishtime (DateTime) = 2024/11/21 10:02:06+00
  firstreporttime (DateTime) = 2019/01/03 14:00:00+00
  querytime (DateTime) = 2024/11/22 07:00:17+00
  caveat (String) = Machine-generated data

and result with CPL_DEBUG=ON (some lines with credentials removed)

OCI: From 'Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production' :
OCI: Version:21
OCI: Release:0
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS.FF TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS.FF'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS.FF TZHTZM'         NLS_NUMERIC_CHARACTERS = '. ')
OCI: From 'Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production' :
OCI: Version:21
OCI: Release:0
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS.FF TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS.FF'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS.FF TZHTZM'         NLS_NUMERIC_CHARACTERS = '. ')
OCI: In Create Layer ...
OCI: Prepare(CREATE TABLE "TEST" ( OGR_FID INTEGER PRIMARY KEY, SHAPE MDSYS.SDO_GEOMETRY ))
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT
FROM user_tab_columns
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))
OCI: Prepare(SELECT COUNT(*)
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t
WHERE u.table_name = :table_name
  AND u.column_name = :geometry_name)
OCI: get dim based of existing data or index failed.
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))
OCI: Prepare(SELECT WKTEXT, AUTH_SRID, AUTH_NAME FROM MDSYS.CS_SRS WHERE SRID = 4326 AND WKTEXT IS NOT NULL)
OCI: Prepare(ALTER TABLE TEST ADD "REPORTTIME" TIMESTAMP(3))
OCI: Prepare(ALTER TABLE TEST ADD "RESULTTIME" TIMESTAMP(3))
OCI: Prepare(ALTER TABLE TEST ADD "PUBLISHTIME" TIMESTAMP(3))
OCI: Prepare(ALTER TABLE TEST ADD "FIRSTREPORTTIME" TIMESTAMP(3))
OCI: Prepare(ALTER TABLE TEST ADD "QUERYTIME" TIMESTAMP(3))
OCI: Prepare(ALTER TABLE TEST ADD "CAVEAT" VARCHAR2(512))
OGR2OGR: Using WriteArrowBatch()
GPKG: Using 4 threads
GPKG: GeoPackage v1.4.0
GPKG: GeoPackage v1.4.0
GPKG: GeoPackage v1.4.0
GPKG: GeoPackage v1.4.0
OCI: Prepare(INSERT /*+ APPEND */ INTO "TEST"("OGR_FID","SHAPE","REPORTTIME","RESULTTIME","PUBLISHTIME","FIRSTREPORTTIME","QUERYTIME","CAVEAT") VALUES ( :fid , :geometry,  :field_0,  :field_1,  :field_2,  :field_3,  :field_4,  :field_5, :field_6) )
OCI: Prepare(SELECT MAX("OGR_FID") FROM "TEST")
OCI: Flushing 100 features on layer TEST
ERROR 1: ORA-01830: date format picture ends before converting entire input string
 in OCIStmtExecute
ERROR 1: WriteArrowBatch() failed
ERROR 1: Terminating translation prematurely after failed
translation of layer TEST (use -skipfailures to skip errors)
GDAL: GDALClose(/u02/efs/TEST_base.gpkg, this=0x55eda2340bb0)

@msmitherdc msmitherdc reopened this Nov 23, 2024
rouault added a commit to rouault/gdal that referenced this issue Nov 23, 2024
@rouault
OCI: add a TIMESTAMP_WITH_TIME_ZONE layer creation option, and ogr2og…
8658542 
…r tweaks

Fixes OSGeo#11057 (comment)
@rouault rouault mentioned this issue Nov 23, 2024
Merged
rouault added a commit to rouault/gdal that referenced this issue Nov 23, 2024
@rouault
OCI: add a TIMESTAMP_WITH_TIME_ZONE layer creation option, and ogr2og…
00e70b5 
…r tweaks

Fixes OSGeo#11057 (comment)
@rouault
Copy link
Member

rouault commented Nov 23, 2024

Just getting to build gdal oci plugin after this update and still getting the error.

ok, could reproduce. Here the issue was related to timezones. Fixed per #11342

rouault added a commit to rouault/gdal that referenced this issue Nov 23, 2024
@rouault
OCI: add a TIMESTAMP_WITH_TIME_ZONE layer creation option, and ogr2og…
f17fe51 
…r tweaks

Fixes OSGeo#11057 (comment)
rouault added a commit to rouault/gdal that referenced this issue Nov 23, 2024
@rouault
OCI: add a TIMESTAMP_WITH_TIME_ZONE layer creation option, and ogr2og…
7fec8c7 
…r tweaks

Fixes OSGeo#11057 (comment)
rouault added a commit that referenced this issue Nov 26, 2024
@rouault @github-actions
OCI: add a TIMESTAMP_WITH_TIME_ZONE layer creation option, and ogr2og…
7b6f92e 
…r tweaks

Fixes #11057 (comment)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rouault rouault

Labels
None yet
Projects
None yet
Milestone
No milestone
3 participants
@msmitherdc @rouault @jratike80