Stack Overflow Asked on November 28, 2020
Spring Boot version ‘2.3.4.RELEASE’
Java 11
org.springframework.boot:spring-boot-starter-jdbc
org.springframework.boot:spring-boot-starter-data-jpa
spring-data-jpa-2.3.4.RELEASE
runtime(mysql:mysql-connector-java)
Server DB MariaDB (ver. 10.5.5-MariaDB)
Java MariaDB Connector J:2.6.0[stable]
I’m trying to persist a java.sql.Timestamp object in Hibernate with millisecond precision. I need save dates to db with milliseconds. For example: 2020-10-08 03:23:38.454.
my domain:
import java.sql.Timestamp;
@Entity
@Data
@Table(name = "date_test")
public class DateTestDomain {
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP(3)")
@Temporal(TIMESTAMP)
private Calendar dateTest;
}
my repo:
@Repository
public interface DateTestRepo extends JpaRepository<DateTestDomain, Timestamp> {
}
save date to db:
private final JdbcTemplate db;
...
long testTime = 1602120218454L;
Timestamp dateTimeStamp = new Timestamp(testTime);
db.update("INSERT INTO date_test" + " (date) VALUES( "" + dateTimeStamp + "")");
UPD: Result of sql is right as I need!!! This method working perfect:
2020-10-08 03:23:38.454
But with hibernate/JPA result is FALSE.
Debug Trace:
2020-10-09 22:26:53.120 Hibernate: insert into date_test (date) values (?)
2020-10-09 22:26:53.122 TRACE 95038 — [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] – [2020-10-09 22:26:53.044]
Calendar calendar = Calendar.getInstance();
Date date = new Date();
calendar.setTimeInMillis(date.getTime());
dateTestDomain.setDateTest(calendar);
dateTestRepo.save(dateTestDomain);
Result of sql: the fractional seconds are always set to .000 with hibernate sql insert:
2020-10-09 22:26:53.000
please help. I need save to db time with millisecond precision throw JPA.
UPD:
I try sql dialect:
org.hibernate.dialect.MySQL5InnoDBDialect than org.hibernate.dialect.MySQL55InnoDBDialect than org.hibernate.dialect.MariaDB103Dialect than org.hibernate.dialect.MariaDB105Dialect
without success.
UPD 1:
Hibernate: INSERT INTO date_test (timestamp, local_date_time, local_date_timea) VALUES (NOW(3), ?, ?)
2020-10-10 15:33:29.099 TRACE 44072 — [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] – [2020-10-10 15:33:29.051]
2020-10-10 15:33:29.100 TRACE 44072 — [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] – [java.util.GregorianCalendar[time=1602336809051,areFieldsSet=true…
Result SQL:
2020-10-10 15:33:29.101, 2020-10-10 13:33:29.000, 2020-10-10 15:33:29.000.
And one more problem:
DB dates:
2020-10-10 16:19:42.578
2020-10-10 16:20:47.000
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
Hibernate: select datetestdo0_.timestamp as timestam1_0_ from date_test datetestdo0_ where datetestdo0_.timestamp>?
binding parameter [1] as [TIMESTAMP] – [2020-10-10 16:20:47.893]
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.9
jdbcsql:
select timestamp from date_test where timestamp>"2020-10-10 16:20:47.893"
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
jpa/hibernate not working with milliseconds…
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(calendar.getTimeInMillis());
Try this one instead of using new Date()
Answered by Onur Baştürk on November 28, 2020
Since JPA 2.2 there is support of java8 date and time API. I have not tried if it will solve your problem or not but can you try with java8's LocalDateTime
instead of Calendar
type.
Replace:
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP")
@Temporal(TIMESTAMP)
private LocalDateTime localDateTime;
Answered by Hiren on November 28, 2020
I suggest you use Instant which models a single instantaneous point on the time-line.
import java.time.Instant;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
public class Main {
public static void main(String[] args) {
Instant now = Instant.now();
System.out.println(now);
// Instant to milliseconds
System.out.println(now.toEpochMilli());
// Milliseconds to Instant
Instant moment = Instant.ofEpochMilli(1602120218454L);
// Print the default representation i.e. Instant#toString
System.out.println(moment);
// Which is same as the following output
String strTimestampDefaultFormat = moment.toString();
System.out.println(strTimestampDefaultFormat);
// Custom representation with milliseconds precision
String strTimestamp = moment.atOffset(ZoneOffset.UTC)
.format(DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS"));
System.out.println(strTimestamp);
}
}
Output from a sample run:
2020-10-08T13:44:46.765240Z
1602164686765
2020-10-08T01:23:38.454Z
2020-10-08T01:23:38.454Z
2020-10-08 01:23:38.454
With strTimestamp
(as obtained above), you can replace
sql.append(" (date) VALUES( "").append(dateTimeStamp).append("")");
with
sql.append(" (date) VALUES( "").append(strTimestamp).append("")");
Answered by Arvind Kumar Avinash on November 28, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP