• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle Timestamps and java.

 
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok guys bascialyl what I am doing is this. I get a date/time from a log file and it is in the format of 2002-11-12 14:09:22.0 Well I do a prepared statement and then inside the db it looks like this...
11/22/2002 2:09:22PM SO when I do a select I have to use 2002-11-12 14:09:22.0 and it does nto return any results. How can I insert it into the db the correct date format.
I tried in my preparedstatement this:
insert into mcserver (create_date, hostname, line_number, type, duration) values (to_date(? , 'yyyy-mm-dd hh24:mm:ss'), ?, ?, ?, ?)";
and then for my timestamp top set the first field:
preparedstatement.setTimestamp(1, mcserverpk.createDate);
Well doign it that way I get the error:
ORA-01810: format code appears twice
I just want to get it to work.
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Anthony, if create_date is a date/time type then use:
"insert into mcserver (create_date, hostname, line_number, type, duration) values ( ? , ?, ?, ?, ? )";
and
preparedstatement.setTimestamp(1, mcserverpk.createDate);
You don't have to do any conversion to date, that's what the setTimestamp() method does.
Jamie
 
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maybe I am missing something but the error message states the problem, doesn't it?
Within your SQL string you specified the to_date() function and you gave the format. Then you call setTimestamp and you give the format again.
Comment out the call to setTimestamp and try it.
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I coment it out, how do I associate the date with the prepared statement. Also Jamie in your post I do that and it works, but I cannot do a select on the date using the format that the log file has. Oracle somehow transforms the date.
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Since the data comes from a file, I think the to_date() approach is more simple. However, setTimestamp is more flexible.
While on the topic, is there an easy way to convert the date from the file to a Timestamp?
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Anthony Smith:
If I coment it out, how do I associate the date with the prepared statement.


You simply use setString.
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, maybe I am not getting enough information. I get the time already as a timestamp and it is in the form of 2002-11-12 14:09:22.0
So I have to use setTimestamp...
It never gets changed but the database still says. 11/22/2002 2:09:22PM
When I do a select I do a setTimestamp but I will only be able to search with the 2002-11-12 14:09:22.0 format.
I do not understand how it is changing
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just to be clear, what is the .0 at the end of the date string?
Also, in the format mask from your original post you use 'mm' for the minutes when it should be 'mi'. That's the reason, Aha
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The .0 is just millisecs.
Even when doing mi I get...
Like I said, I get the value in a timestamp so I have to use setTimestamp.
So even changing to mi I sitll will get
ORA-01810: format code appears twice
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Anthony, I think you are getting a little confused, and it can be very confusing! The main principle that I think you may be missing is that a date does not have a format. Different languages and databases have their own internal(efficient) way to deal with what a date is. In java, it is a long value. There is no format built in to the date value. Usually, each vendor will have some sort of default way to display the internal number, but they are never the same!! What you need to do is format the date the way you want it, when you want to view it.
So use the setTimestamp() method to insert dates, then when you want to view it you can use a SimpleDateFormat to convert it to any display form you like:
eg.

[ September 23, 2003: Message edited by: Jamie Robertson ]
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I see hwta you are saying but my probem is that I have to use the date in my select statement. My date format does nto match what is in the db. Becasue I am given a timestamp I have to use setTimestamp. My timestamp is the same as what I wanted to enter in the db but it never stores as such.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Anthony Smith:
I see hwta you are saying but my probem is that I have to use the date in my select statement. My date format does nto match what is in the db. Becasue I am given a timestamp I have to use setTimestamp. My timestamp is the same as what I wanted to enter in the db but it never stores as such.


Anthony, it is never stored in any format at the DB end. That's my point. If you want to override Oracle's default formatting use the to_char ( date, "< format that you want to see it >" ) in your select statement and use the ResultSet.getString() method to retrieve the value from the database.
Remember you can always specify what format a date is displayed rather than relying on the default formats( both in java and in Oracle ).
What is the other scenario you are having problems with( I think you have the insert working ). If you post the details, maybe we can walk through the specific problem and through the specific problem you can see the solution.
Example:
-have a String timestamp in a log_file and other data
-need to search the create_date ( timestamp field ) for matches on the log_file date
Jamie
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, here is the code:
preparedstatement = connection.prepareStatement("select create_date, hostname, line_number from mcserver where create_date = ? AND hostname = ? AND line_number = ?");
preparedstatement.setTimestamp(1, mcserverpk.createDate);
How do I implement the to_char method?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I see hwta you are saying but my probem is that I have to use the date in my select statement.
there are a few ways to tackle this problem:
1 - take your Date value from the database and convert it to a character field ( to_char( create_date, "< log file format >" in the same format as the log file, then compare Strings

or
convert the log file String date to a java date/timestamp and compare:

there are other variations, but that should help
Jamie
[ September 23, 2003: Message edited by: Jamie Robertson ]
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think I see what you are syang but it is already a Timestamp. I never recive it as a string. How can I reformat the timestamp?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Anthony Smith:
I think I see what you are syang but it is already a Timestamp. I never recive it as a string. How can I reformat the timestamp?


if it refers to the log file timestamp, then just delete the conversion. This is what you need!

Jamie
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
now a much slower comparison using String can be done as well, which is what I think you're getting at, so here it is:
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Anthony Smith:
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.


Alright, so the real issue is that you are doing a select using timestamps, and your not getting any results. So you check to see if it is there, but on the select, it shows up in Oracle's default format. The problem here is not that they are in different formats, but that the timestamps are not equal. I don't know if this is a rounding/precision error when the log file date value was written to the file or on the insert to the database, but the values are different, if only by nanoseconds ( nanos not being set on your timestamp --> see the note about this here ). So your best bet might be to convert to Strings ( see previous post ) at a lesser precision to accomodate this discrepancy...
other than that I'm all out of ideas
[ September 23, 2003: Message edited by: Jamie Robertson ]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Anthony Smith:
You know that is exactly what I have but... The format that I have is not the same format in the db. Thats why my queries show up null.


I thought you were still stuck on getting the query to execute, not the fact that it was running but with no results...little mix up there.
reply
    Bookmark Topic Watch Topic
  • New Topic