Hi Ivan,
> -----Original Message-----
> From: Ivan R. Toledo Ivanovic
>
> Hi. I've been struggling with Oracle datastore for a few days.
>
> My problem is: I've a table with a TIMESTAMP column (of type
> DATE) and a SDO_GEOMETRY column. The DATE type in Oracle can
> also contain hours-minutes-seconds.
>
> If I want to filter (CQL) using that TIMESTAMP column,
> geoserver gives me
> this:
>
> -----
> 2008-07-07 12:41:59,810 ERROR [data.jdbc] - Error Performing
> SQL query: SELECT "ID", "TIMESTAMP", "TIMESTAMP", "GEOM"
> FROM "GPSLOG" WHERE ((1 = 1 AND ("ID" = 2392299 AND
> ("TIMESTAMP" > '2008-07-06' AND "TIMESTAMP" <
> '2008-07-07'))) AND
> SDO_RELATE("GEOM",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO
> _ELEM_INFO_ARRA
> Y(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-72.23157287597657,-34.4
> 2786743164063,-
> 70.68112731933594,-33.39240600585938)),'mask=anyinteract
> querytype=WINDOW') = 'TRUE' )
> java.sql.SQLException: ORA-01861: el literal no coincide con
> la cadena de formato
>
>
> It seems that Geoserver (Geotools?) can't filter
> against DATE columns. When I first declared the feature on
> Geoserver, it gives a type of "(xml fragment)" for the DATE
> column, and:
>
> <!-- definition for class java.sql.Date -->
>
> As the fragment. I changed that to dateTime and it doesn't
> work. This works properly on PostGIS, using the same table structure.
Java.sql.Date is fine for a timestamp - it stores the value of
milliseconds since Jan 1, 1970 00:00:00.000.
The problem here is that the SQL statement is comparing a Timestamp
object with a String. '2008-07-06' should actually be prepared as {d
'2008-07-06'} or even better, to_date('2008-07-06','yyyy-mm-dd').
Oracle will try to convert your String '2008-07-06' to a date behind the
scenes, but only tries using its default date format, which at least up
until oracle 10gR2 uses a 2 digit year. Try your filter using
'06-Jul-08' (without quotes).
This is actually of course similar to the Y2K bug. With Oracle 10gR2 at
least, this as a method to compare dates will work as expected when
comparing dates between 1950 and 2049. This means that Geoserver also
will only be able to use this date range with Oracle.
Examples:
SELECT to_char(to_date('1950/01/02', 'yyyy/mm/dd'),'YYYY') FROM dual
WHERE to_date('1950/01/02', 'yyyy/mm/dd') > '01-Jul-50';
-- Returns no rows
SELECT to_char(to_date('2050/01/02', 'yyyy/mm/dd'),'YYYY') FROM dual
WHERE to_date('2050/01/02', 'yyyy/mm/dd') < '01-Jul-51';
-- Returns no rows
SELECT to_char(to_date('2050/01/02', 'yyyy/mm/dd'),'YYYY') FROM dual
WHERE to_date('2050/01/02', 'yyyy/mm/dd') > '01-Jul-50';
-- Returns the year 2050
So, you will be fine if your dates fall within 1950 - 2049, for now. But
in the long term the Oracle datastore should be updated to prepare dates
properly. I'd do it myself if I had the time!
Regards,
Miles Jordan
Applications Developer
The Australian Antarctic Division
>
> Any hints/ideas?
>
> Thanks,
> Ivan Toledo
> MovilData Chile
>
___________________________________________________________________________
Australian Antarctic Division - Commonwealth of Australia
IMPORTANT: This transmission is intended for the addressee only. If you are not the
intended recipient, you are notified that use or dissemination of this communication is
strictly prohibited by Commonwealth law. If you have received this transmission in error,
please notify the sender immediately by e-mail or by telephoning +61 3 6232 3209 and
DELETE the message.
Visit our web site at http://www.antarctica.gov.au/
___________________________________________________________________________