Github user ijokarumawak commented on a diff in the pull request:
https://github.com/apache/nifi/pull/1983#discussion_r127387210
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/PutSQL.java
---
@@ -828,10 +835,46 @@ private void setParameter(final PreparedStatement
stmt, final String attrName, f
stmt.setBigDecimal(parameterIndex, new
BigDecimal(parameterValue));
break;
case Types.DATE:
- stmt.setDate(parameterIndex, new
Date(Long.parseLong(parameterValue)));
+ Date date;
+
+ if (valueFormat.equals("")) {
+ if
(LONG_PATTERN.matcher(parameterValue).matches()) {
+ date = new
Date(Long.parseLong(parameterValue));
+ } else {
+ String dateFormatString = "yyyy-MM-dd";
+
+ SimpleDateFormat dateFormat = new
SimpleDateFormat(dateFormatString);
+ java.util.Date parsedDate =
dateFormat.parse(parameterValue);
+ date = new Date(parsedDate.getTime());
+ }
+ } else {
+ final DateTimeFormatter dtFormatter =
getDateTimeFormatter(valueFormat);
+ LocalDate parsedDate =
LocalDate.parse(parameterValue, dtFormatter);
+ date = new
Date(Date.from(parsedDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant()).getTime());
+ }
+
+ stmt.setDate(parameterIndex, date);
break;
case Types.TIME:
- stmt.setTime(parameterIndex, new
Time(Long.parseLong(parameterValue)));
+ Time time;
+
+ if (valueFormat.equals("")) {
+ if
(LONG_PATTERN.matcher(parameterValue).matches()) {
+ time = new
Time(Long.parseLong(parameterValue));
+ } else {
+ String timeFormatString = "HH:mm:ss.SSS";
+
+ SimpleDateFormat dateFormat = new
SimpleDateFormat(timeFormatString);
+ java.util.Date parsedDate =
dateFormat.parse(parameterValue);
+ time = new Time(parsedDate.getTime());
+ }
+ } else {
+ final DateTimeFormatter dtFormatter =
getDateTimeFormatter(valueFormat);
+ LocalTime parsedTime =
LocalTime.parse(parameterValue, dtFormatter);
+ time = Time.valueOf(parsedTime);
--- End diff --
Some databases support fractional seconds (milli or microseconds). By
creating java.sql.Time instance from LocalTime, even if the LocalTime has
fractional seconds, it will be truncated because Time.valueOf only uses hours,
minutes and seconds as follows:
```java
java.sql.Time
public static Time valueOf(LocalTime time) {
return new Time(time.getHour(), time.getMinute(), time.getSecond());
}
```
Can we have this like this instead? This way, we can preserve time
precision at milliseconds if the database driver and database server supports
it:
```java
final DateTimeFormatter dtFormatter = getDateTimeFormatter(valueFormat);
LocalTime parsedTime = LocalTime.parse(parameterValue, dtFormatter);
LocalDateTime localDateTime = parsedTime.atDate(LocalDate.ofEpochDay(0));
Instant instant = localDateTime.atZone(ZoneId.systemDefault()).toInstant();
time = new Time(instant.toEpochMilli());
```
I confirmed this behavior with MySQL and PostgreSQL. With PostgreSQL,
before changing this, when I passed "18:25:43.511" JSON value with
ISO_LOCAL_TIME format, it's stored without milliseconds, "18:25:43". With above
change, using new Time(Long) constructor, I was able to preserve milliseconds
"18:25:43.511".
Unfortunately MySQL JDBC Driver has a known issue that it truncates
milliseconds [76775](https://bugs.mysql.com/bug.php?id=76775), so we can't
store millisecond with MySQL currently (TIMESTAMP works though).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
