1

I have a java.lang.String which contains a timestamp with microseconds precision. I want to compare this timestamp with a TIMESTAMP column in an Oracle database backend via JPA

(In query.setParameter method argument)

Query:

select CUSTOMER_NAME from CUSTOMER where REG_TIME < timestamp_which_i_want_to_pass;

When I referred in the answer given in the stackoverflow_link I understood that javatype to be compared with DB timestamp should be either java.util.Date or java.util.Calendar.

My problem is that if I convert the timeStamp(in String) to Date/Calendar, I will lose microseconds. But I need accurate comparison.

How to achieve this comparison without losing microseconds?

riskop
  • 1,693
  • 1
  • 16
  • 34
Alagammal P
  • 829
  • 5
  • 19
  • 43

3 Answers3

0

There are few possible ways :

1) Change value type that contains timestamp with microseconds in DB to long, then convert your TIMESTAMP to long as well and add there microseconds populated with 000. For example :

long timestampWithoutMicroSeconds = 1496218445;
long timestampWithMicroseconds = timestampWithoutMicroSeconds * 1000;

Then just use your query to select correct results.

2) Depending on your DB you can use contains like operator. It means that you have to convert search parameter (TIMESTAMP) into String and use query like this :

WHERE `column` LIKE '%part%'

It means that you will select all records where column contains 'part'. Example :

|username|time_with_micro    |
|+------+|+------------------|
| test   | 1496218445123     |

Select * from table where time_with_micro like %1496218445%

It will select your value.

Also, using this approach you have to play with % sign as it define unknown part. In your task you need select all records where your time start with your search parameter to avoid collisions. Then it will do exactly what you are looking for, all records with time bigger (>) then you are looking for will be selected.

Andriy Rymar
  • 313
  • 3
  • 8
  • 1. The DB coloumn cannot be modified as it is existing 2. I want to select teh records where the TImestamp column contains lesser value than the one I want to pass. (Not sure If like can accuratle fetch the results) – Alagammal P May 31 '17 at 09:55
  • You can write a script that would add one additional column with `long` type and value from your `String` column, and then drop that `String` column. – Andriy Rymar May 31 '17 at 10:30
  • Without changing column type you cannot use < > operators with `String` type. Just contains (start with / end with) or equals. – Andriy Rymar May 31 '17 at 10:31
0

You can convert the parameter type with the steps below:

String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format( param);  
Timestamp time = Timestamp.valueOf(date);

I hope this helps.

Satan Pandeya
  • 3,747
  • 4
  • 27
  • 53
Depp
  • 36
  • 3
0

Nor java.util.Date nor java.util.Calendar has microsecond precision, so forget using them as parameters!

Use java.sql.Timestamp, which has nanosecond precision. In this case be careful to set the nano field on the Timestamp ( java.sql.Timestamp way of storing NanoSeconds )

I created a simple scenario for testing:

CREATE TABLE precisetimes(id NUMBER(2), precisetime TIMESTAMP(6));
insert into precisetimes values(1, TO_TIMESTAMP('2017-05-31 12:12:12.123456','YYYY-MM-DD HH:MI:SS.FF'));
insert into precisetimes values(2, TO_TIMESTAMP('2017-05-31 12:12:12.12','YYYY-MM-DD HH:MI:SS.FF'));
insert into precisetimes values(3, TO_TIMESTAMP('2017-05-31 12:12:12.123457','YYYY-MM-DD HH:MI:SS.FF'));
commit;

select id,TO_CHAR(precisetime, 'YYYY-MM-DD HH:MI:SS.FF') ptime from precisetimes;

selecting all:

        ID PTIME                       
---------- -----------------------------
         1 2017-05-31 12:12:12.123456   
         2 2017-05-31 12:12:12.120000   
         3 2017-05-31 12:12:12.123457

After this preparation the following code:

TypedQuery<Precisetimes> q = entityManager.createQuery(
        "select p from Precisetimes p where p.precisetime < :param", 
        Precisetimes.class);
Timestamp ts;
ts = Timestamp.valueOf("2017-05-31 12:12:12.123456");
q.setParameter("param", ts, TemporalType.TIMESTAMP);
System.out.println("result: " + q.getResultList());
ts = Timestamp.valueOf("2017-05-31 12:12:12.123457");
q.setParameter("param", ts, TemporalType.TIMESTAMP);
System.out.println("result: " + q.getResultList());
ts = Timestamp.valueOf("2017-05-31 12:12:12.123458");
q.setParameter("param", ts, TemporalType.TIMESTAMP);
System.out.println("result: " + q.getResultList());

has this result:

result: [id: 2, precisetime: 2017-05-31 12:12:12.12]
result: [id: 1, precisetime: 2017-05-31 12:12:12.123456, id: 2, precisetime: 2017-05-31 12:12:12.12]
result: [id: 1, precisetime: 2017-05-31 12:12:12.123456, id: 2, precisetime: 2017-05-31 12:12:12.12, id: 3, precisetime: 2017-05-31 12:12:12.123457]

I guess this is what you need? I uploaded the test example here: http://peter.risko.hu/java_incubator/jpa_hibernate_oracle_precisetimestamp.zip

Note that there's an other way round, converting the Oracle timestamp to String in the Select statement via TO_CHAR function:

select id,TO_CHAR(precisetime, 'YYYY-MM-DD HH:MI:SS.FF') ptime from precisetimes 
  where TO_CHAR(precisetime, 'YYYY-MM-DD HH:MI:SS.FF') > '2017-05-31 12:12:12.123457';

result:

        ID PTIME                       
---------- -----------------------------
         1 2017-05-31 12:12:12.123456   
         2 2017-05-31 12:12:12.120000

BUT for this you have to use JPA's Criteria api or native query because native functions are not supported in JPQL. In this case also be careful to use the same format on the DB side and on the Java side.

riskop
  • 1,693
  • 1
  • 16
  • 34
  • 1
    Any idea how to translate TO_CHAR in JPA? – Alagammal P May 31 '17 at 11:03
  • Alagammal, sorry, I just now realized that you use JPQL. You have to use the JPA's Criteria API or native query for TO_CHAR. JPQL doesn't support native functions. – riskop May 31 '17 at 11:22