CODEWITHSUNDEEP
javasqloracle-databasehibernatedate
JibZ
JibZ

Reputation: 143

ORACLE SQL Difference between two dates hibernate

I need to calculate the difference between two dates into SQL dialect into my hibernate repository, i tried many solutions but for now it's not working, i keep getting this error:

org.hibernate.MappingException: No Dialect mapping for JDBC type: -104

NOTE: The query is working directly into DBeaver in SQL, but not into Spring.

I'm working with Springboot, hibernate, oracle.

I have to expose the calculated new time (as "Duration") trough my springboot REST controller into json like this format: HH:mm:ss The idea is that i have a datatable on the front-end side, showing some info into a table, and i would like to show the calculated time.

Here is my repository:

public interface BatchInfosJoinRepository extends JpaRepository<BatchInfosJoin, Long> {
    @Query(value = "SELECT ji.JOB_NAME, bse.STEP_NAME, bse.STATUS,bse.START_TIME, bse.END_TIME, bse.END_TIME - bse.START_TIME AS DATE_DIFF " +
            " FROM BATCH_JOB_EXECUTION bje INNER JOIN BATCH_STEP_EXECUTION bse ON bje.JOB_EXECUTION_ID = bse.JOB_EXECUTION_ID " +
            " INNER JOIN BATCH_JOB_INSTANCE ji ON ji.JOB_INSTANCE_ID = bje.JOB_INSTANCE_ID", nativeQuery = true)
    Set<Object[]> fetchStepJoin();
}

My model entity "BatchInfoJoin":

@Entity
public class BatchInfosJoin {

    @Id
    @Column(name = "step_execution_id")
    private Long stepExecutionId;

    @Column(name = "job_name")
    private String jobName;
    @Column(name = "step_name")
    private String stepName;
    @Column(name = "status")
    private String status;
    @Column(name = "start_time")
    private Date startTime;
    @Column(name = "end_time")
    private Date endTime;
    @Column(name = "date_diff")
    private Date dateDiff;
}

Or maybe should i calculate it using an special "Util" class in Java?

Upvotes: 1

Views: 668

Answers (1)

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79580

Replace

bse.END_TIME - bse.START_TIME AS DATE_DIFF

with

(bse.END_TIME - bse.START_TIME)*60*60*24 AS DATE_DIFF

Upvotes: 1

Related Questions