You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Peter Sch�tt <ne...@pstt.de> on 2013/05/08 15:35:53 UTC
Oracle Timestamp in SOLR
Hallo,
I have a field with the type TIMESTAMP(6) in an oracle view.
When I want to import it directly to SOLR I get this error message:
WARNING: Error creating document : SolrInputDocument[oid=12,
last_action_timestamp=oracle.sql.TIMESTAMP@34907781, status=2, ...]
org.apache.solr.common.SolrException: Invalid Date
String:'oracle.sql.TIMESTAMP@
34907781'
at org.apache.solr.schema.DateField.parseMath(DateField.java:182)
at org.apache.solr.schema.TrieField.createField(TrieField.java:616)
at org.apache.solr.schema.TrieField.createFields
(TrieField.java:655)
What is the best way to import it?
This way works but I do not know if this is the best practise:
In the query:
TO_CHAR(LAST_ACTION_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as LAT
For the field:
<field column="LAT" name="last_action_timestamp"
dateTimeFormat="yyyy-MM-dd hh:mm:ss" />
Conversion from timestamp to string to timestamp seems to me not a good
way. Is there a better way?
Thanks for any hints.
Ciao
Peter Sch�tt
Re: Oracle Timestamp in SOLR
Posted by Peter Sch�tt <ne...@pstt.de>.
Hallo,
>
>: > SELECT ... CAST(LAST_ACTION_TIMESTAMP AS DATE) AS LAT
>:
>: This removes the time part of the timestamp in SOLR. althought it is
>: shown in PL/SQL-Developer (Tool for Oracle).
>
> Hmmm... that makes no sense to me based on 10 seconds of googling...
>
> http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i184
> 7
>
> "The DATE datatype stores the year (including the century), the month,
> the day, the hours, the minutes, and the seconds"
>
> ...but i'll take your word for it.
>
>: The only way I found in the net is to write an own converter :-(
>
> There must be *some* way to either tweak your SQL or tweak your JDBC
> connection properties such that Oracle's JDBC driver will give you a
> legitimate java.sql.Date or java.sql.Timestamp instead of it's own
> internal class (that doesn't extend java.util.Date) ... otherwise it's
> just total freaking anarchy.
Perhaps it is really an oracle problem.
I found also this solution:
http://stackoverflow.com/questions/10111517/getting-correct-time-from-
oracle-date-in-solr-dataimporthandler
but I did not get it to run. So I keep my timestamp - string - date way
(which I do not really like).
Ciao
Peter Sch�tt
Re: Oracle Timestamp in SOLR
Posted by Shawn Heisey <so...@elyograg.org>.
On 5/16/2013 11:00 AM, Chris Hostetter wrote:
> There must be *some* way to either tweak your SQL or tweak your JDBC
> connection properties such that Oracle's JDBC driver will give you a
> legitimate java.sql.Date or java.sql.Timestamp instead of it's own
> internal class (that doesn't extend java.util.Date) ... otherwise it's
> just total freaking anarchy.
Looks like you can use the V8Compatible connection property or upgrade
the oracle jdbc driver. Upgrading the driver is probably the best option.
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01
Thanks,
Shawn
Re: Oracle Timestamp in SOLR
Posted by Chris Hostetter <ho...@fucit.org>.
: > SELECT ... CAST(LAST_ACTION_TIMESTAMP AS DATE) AS LAT
:
: This removes the time part of the timestamp in SOLR. althought it is shown
: in PL/SQL-Developer (Tool for Oracle).
Hmmm... that makes no sense to me based on 10 seconds of googling...
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847
"The DATE datatype stores the year (including the century), the month, the
day, the hours, the minutes, and the seconds"
...but i'll take your word for it.
: The only way I found in the net is to write an own converter :-(
There must be *some* way to either tweak your SQL or tweak your JDBC
connection properties such that Oracle's JDBC driver will give you a
legitimate java.sql.Date or java.sql.Timestamp instead of it's own
internal class (that doesn't extend java.util.Date) ... otherwise it's
just total freaking anarchy.
-Hoss
Re: Oracle Timestamp in SOLR
Posted by Peter Sch�tt <ne...@pstt.de>.
Hallo,
>: I have a field with the type TIMESTAMP(6) in an oracle view.
> ...
>: What is the best way to import it?
> ...
>: This way works but I do not know if this is the best practise:
> ...
>: TO_CHAR(LAST_ACTION_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as
>: LAT
>
> instead of having your DB convert to a string, and then forcing DIH to
> parse that string, try asking your DB to cast to something that JDBC
> will respect as a Date object when DIH fetches the results
>
> I don't know much about oracle, but perhaps something like...
>
> SELECT ... CAST(LAST_ACTION_TIMESTAMP AS DATE) AS LAT
This removes the time part of the timestamp in SOLR. althought it is shown
in PL/SQL-Developer (Tool for Oracle).
The only way I found in the net is to write an own converter :-(
Thanks in advance for any other hints.
Ciao
Peter Sch�tt
Re: Oracle Timestamp in SOLR
Posted by Chris Hostetter <ho...@fucit.org>.
: I have a field with the type TIMESTAMP(6) in an oracle view.
...
: What is the best way to import it?
...
: This way works but I do not know if this is the best practise:
...
: TO_CHAR(LAST_ACTION_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as LAT
instead of having your DB convert to a string, and then forcing DIH to
parse that string, try asking your DB to cast to something that JDBC will
respect as a Date object when DIH fetches the results
I don't know much about oracle, but perhaps something like...
SELECT ... CAST(LAST_ACTION_TIMESTAMP AS DATE) AS LAT
-Hoss
Re: Oracle Timestamp in SOLR
Posted by Michael Della Bitta <mi...@appinions.com>.
Peter,
Looks like you can call timestampValue() on that object and get back a
java.sql.Timestamp, which is a subclass of java.util.Date:
http://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/sql/TIMESTAMP.html#timestampValue__
Hope that helps,
Michael Della Bitta
------------------------------------------------
Appinions
18 East 41st Street, 2nd Floor
New York, NY 10017-6271
www.appinions.com
Where Influence Isn’t a Game
On Wed, May 8, 2013 at 9:35 AM, Peter Schütt <ne...@pstt.de> wrote:
> Hallo,
> I have a field with the type TIMESTAMP(6) in an oracle view.
>
> When I want to import it directly to SOLR I get this error message:
>
> WARNING: Error creating document : SolrInputDocument[oid=12,
> last_action_timestamp=oracle.sql.TIMESTAMP@34907781, status=2, ...]
> org.apache.solr.common.SolrException: Invalid Date
> String:'oracle.sql.TIMESTAMP@
> 34907781'
> at org.apache.solr.schema.DateField.parseMath(DateField.java:182)
> at org.apache.solr.schema.TrieField.createField(TrieField.java:616)
> at org.apache.solr.schema.TrieField.createFields
> (TrieField.java:655)
>
> What is the best way to import it?
>
>
> This way works but I do not know if this is the best practise:
>
> In the query:
>
> TO_CHAR(LAST_ACTION_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as LAT
>
> For the field:
>
> <field column="LAT" name="last_action_timestamp"
> dateTimeFormat="yyyy-MM-dd hh:mm:ss" />
>
> Conversion from timestamp to string to timestamp seems to me not a good
> way. Is there a better way?
>
> Thanks for any hints.
>
> Ciao
> Peter Schütt
>
>