Select record where a number timestamp is in between 2 java dates-CodePudding

I want to select in SQL a record from my oracle table based on a fromDate and toDate variables.

I'm having troubles with the where clause because the TIMESTMP column is declared :

  CREATE TABLE SCHEMA.LOGGING_EVENT
(
  TIMESTMP           NUMBER(20)                 NOT NULL,

   [...]

How should the query be?

Additional info: fromDate and toDate will arrive from a java backend while instead the TIMESTMP column is populated by logback (DBAppender) and can't be changed in format or type.

my try (doesn't work):

select * from LOGGING_EVENT LE 
where LE.TIMESTMP>= to_number('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') 
AND LE.TIMESTMP<= to_number('12-01-2023 21:24:00', 'dd-mm-yyyy hh24:mi:ss');

Thanks

CodePudding user response：

Values you've stored look like Unix epoch time. If so, then

select *
from logging_event_le
where le.timestmp / 1000 between (to_date( '12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') - date '1970-01-01') * 24 * 60 * 60
                             and (to_date( '12-01-2023 21:24:00', 'dd-mm-yyyy hh24:mi:ss') - date '1970-01-01') * 24 * 60 * 60

Why dividing timestmp by 1000? Because it has 13 digits; if it had 10 digits, it would represent date up to seconds; with 13 digits, you have miliseconds so - remove them.

As of the right side of between: use to_date function with appropriate format model to convert string ('12-01-2012 21:24:00' is a string) to date; then subtract 01st of January 1970 to get number of days between those two dates and multiply result by (24 * 60 * 60) to get number of seconds (as day has 24 hours, every hour has 60 minutes and every minute has 60 seconds). Why? Because Unix expoch time represents number of seconds that have passed since UTC midnight of 1st of January 1970.

CodePudding user response：

As Java was mentioned and assuming that fromDate and toDate are of type java.util.Date, you could try:

String sql = 
    "SELECT * FROM LOGGING_EVENT LE "  
    " WHERE LE.TIMESTMP BETWEEN ? AND ?";

try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setLong(1, fromDate.getTime());
    stmt.setLong(2, toDate.getTime());
    // execute query and process result
}