Reading Timestamp column from Oracle using Java.. rs.getString(index)
While reading a Timestamp column data from Oracle 10g using Java 6, I read it as follows:
String str = rs.getString(index);
I don't want to use rs.getTimestamp() . Now, when I do the same with a Date column, I get an appropriate String representation of Date. But with Timestamp I get a String as follows:
2009-5-3 12:36:57. 618000000
I don't understand why a space is introduced just before the milliseconds. This makes the timestamp I read unparseable by pre-defined timestamp formats. Any idea as to why am I getting a space before milliseconds?
好吧，这就是我所要做的，对我来说确实是一个可行的解决方案。
ResultSet rs = st.executeQuery("..");
ResultSetMetaData rsmt = rs.getMetaData();
int columnIndex = 0;
while (rs.next()) {
if (rsmt.getColumnType(columnIndex) == 93) { // ts field - ref. -> http://www.devdaily.com/java/edu/pj/jdbc/recipes/ResultSet-ColumnType.shtml
Timestamp tsTemp = this.getTimestamp(columnIndex);
return tsTemp.toString(); // this does give the timestamp in correct format
}
columnIndex ++;
}
You're doing an implicit conversion to a string, so the formatting is determined by your database session's NLS_TIMESTAMP_FORMAT setting, which is derived from your Java Locale. That is a weird format though, so perhaps it's somehow being overridden.
As you often don't have control over such things, and you might get an unexpected change in an environment that breaks your code, it's generally safer to specify the format in your query. If you really need to retrieve it with getString() , rather than
select timestamp_field from ...
do something like
select to_char(timestamp_field, 'YYYY-MM-DD HH24:MI:SS.FF3') from ...
Alternatively you could change your Locale; or set the format in the session by executing an alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3' before your query, but you'd have to do it in every session (or use a logon trigger) and it could potentially have unexpected impacts elsewhere, particularly if using connection pooling. Explicitly getting the data out of the database in the format you want is more obvious, and won't break if someone else changes something later.
上一篇: 跨多个日期选择特定时间戳记间隔