英文:
How to format Oracle's Interval Datatype to HH:MM format?
问题
我在Oracle数据库中有一个表，其中包含以下列：
START_TIME INTERVAL DAY(0) TO SECOND(0)
我正在运行一个基本的选择查询来从数据库中获取值。我正在使用JDBC模板和类似以下方式的getString()函数：
String startTime = rs.getString("START_TIME");
当数据库中有+00 11:00:00.000000时，rs.getString("START_TIME")获取到的是0 11:0:0.0
而当有+00 09:30:00.000000时，获取到的是0 9:30:0.0，但我的要求是11:00和09:30
有人可以帮我解决如何进行这种格式化吗？
我已经尝试过以下方法：
如何在Java中表示Oracle间隔 - 但我正在使用基于Swagger OpenAPI YAML生成DTOs，并且不知道如何实现这一点。而且，我没有使用Hibernate。这是纯粹的JDBC模板。
我的先前问题 - 但它没有帮助我。
英文:
I have a table in Oracle DB with below column
START_TIME INTERVAL DAY(0) TO SECOND(0)
I am running a basic select query to fetch values from DB. I am using JDBC template and getString() like below
String startTime = rs.getString("START_TIME");
When I have +00 11:00:00.000000 in the DB, the rs.getString("START_TIME") fetches 0 11:0:0.0
and when +00 09:30:00.000000 it fetches 0 9:30:0.0 but my requirement is 11:00 and 09:30
Can someone help me with how can I do this formatting?
I have already tried the below:
How to represent Oracle Interval in Java - but I am using swagger open API yaml based generation for DTOs and don't know how can I achieve this. Also, I am not using hibernate. It's plain JDBC Template.
My previous question - but it didn't help me
答案1
得分: 0
你可以尝试以下其中一个变体：
// 添加此函数：将intervalDS转换为Time/Timestamp
public static Time intervalDStoTime(INTERVALDS intervalds) throws ParseException {
SimpleDateFormat intervalFormat = new SimpleDateFormat("dd hh:mm:ss.SSS");
java.util.Date parsedDate = intervalFormat.parse(intervalds.stringValue());
Time time = new java.sql.Time(parsedDate.getTime());
// 或者，如果需要时间戳，可以将上一行替换为以下内容：
// Timestamp timestamp = new java.sql.Timestamp(parsedDate.getTime());
return time;
}
//...
// 你的代码:
// 原始:
oracle.sql.INTERVALDS startTime = rs.getINTERVALDS("START_TIME");
System.out.println("原始:" + startTime);
// 1: 将interval转换为时间:
Time time = intervalDStoTime(startTime);
System.out.println("转换为时间: " + time);
// 2: + 格式化
System.out.println("转换为时间 + 格式化: " + new SimpleDateFormat("hh:mm").format(time));
// 3: 格式化字节
byte[] bytes = startTime.toBytes();
int hour = bytes[4] - 60;
int minute = bytes[5] - 60;
String hhmm = String.format("%02d:%02d", hour, minute);
System.out.println("toBytes + 格式化: " + hhmm);
英文:
You can try one of these variants:
// add this function: it converts intervalDS to Time/Timestamp
public static Time intervalDStoTime(INTERVALDS intervalds) throws ParseException {
SimpleDateFormat intervalFormat = new SimpleDateFormat("dd hh:mm:ss.SSS");
java.util.Date parsedDate = intervalFormat.parse(intervalds.stringValue());
Time time = new java.sql.Time(parsedDate.getTime());
// or you can replace previous line with this one if yu will need timestamp:
// Timestamp timestamp = new java.sql.Timestamp(parsedDate.getTime());
return time;
}
//...
// your code:
//Original:
oracle.sql.INTERVALDS startTime = rs.getINTERVALDS("START_TIME");
System.out.println("Original:" + startTime);
// 1: interval to time:
Time time = intervalDStoTime(startTime);
System.out.println("ToTime: " + time);
// 2: + format
System.out.println("ToTime + format: " + new SimpleDateFormat("hh:mm").format(time));
// 3: format bytes
byte[] bytes = startTime.toBytes();
int hour = bytes[4] - 60;
int minute = bytes[5] - 60;
String hhmm = String.format("%02d:%02d", hour, minute);
System.out.println("toBytes + format: " + hhmm);
专注分享java语言的经验与见解，让所有开发者获益！
评论