Date difference in date in Oracle and Java

following problem is bothering me and I can't find any reasonable explanation and a solution. Perhaps someone could enlighten me.

I have an application that calculates date difference (in days) - in a Search mask and in a Details mask. In the first mask I use days to filter out records with a duration search criteria (Search mask) and I do this with an SQL query:

WHERE...
...
AND DECODE(TRUNC(to_number(SUBSTR((close_date - 
create_date),1,instr(close_date - create_date,' ')))), NULL, 
TRUNC(to_number(SUBSTR((systimestamp - create_date),1,instr(systimestamp - 
create_date,' ')))), TRUNC(to_number(SUBSTR((close_date - 
create_date),1,instr(close_date - create_date,' ')))) ) >=140

AND DECODE(TRUNC(to_number(SUBSTR((close_date - 
create_date),1,instr(close_date - create_date,' ')))), NULL, 
TRUNC(to_number(SUBSTR((systimestamp - create_date),1,instr(systimestamp - 
create_date,' ')))), TRUNC(to_number(SUBSTR((close_date - 
create_date),1,instr(close_date - create_date,' ')))) ) <=140

In this special case I try to find out all the records that have a duration of 140 days.

In the second mask (Details) I show the record details, including its duration. This is done with the following java code:

public static Integer getDuration(Date caseDate, Date closeDate) {
    Date beginDate = caseDate;
    Date endDate = (closeDate != null)? closeDate: new Date();

    long difference = endDate.getTime() - beginDate.getTime();   
    int daysDiff = (int) (difference / (24.0 * 60开发者_开发知识库 * 60 * 1000));  
    return new Integer(daysDiff);
}

My problem is that when I search, I find some records that do correspond to the search criteria. For instance I find 4 records and all of them have a duration of 140 days. That's according to Oracle. But when I open the Details mask for some of them I get a duration of 139 days for example. So somehow Java and Oracle calculate date differences in a different way. It seems that in Oracle some rounding is being made, but I can't find where this happens. So any suggestions would be helpful. Thanks!

Greets, almaak

The date could be the same but the time could be different. Resulting in 139 days when you calculate through milliseconds. (java)

I suggest not using millis but use the days to calculate.

Something like

public long daysBetween(Calendar startDate, Calendar endDate) {
   Calendar date = (Calendar) startDate.clone();
   long daysBetween = 0;
   while (date.before(endDate)) {
      date.add(Calendar.DAY_OF_MONTH, 1);
      daysBetween++;
   }}

or

/**
     *
     * @param c1 from
     * @param c2 to
     * @return amount of days between from and to
     */
    public int diff(Calendar c1, Calendar c2) {
        int years = c2.get(Calendar.YEAR) - c1.get(Calendar.YEAR);
        if (years == 0) {
            return c2.get(Calendar.DAY_OF_YEAR) - c1.get(Calendar.DAY_OF_YEAR);
        } else {
            Calendar endOfYear =  Calendar.getInstance();
            endOfYear.set(Calendar.YEAR, c1.get(Calendar.YEAR));
            endOfYear.set(Calendar.MONTH, 11);
            endOfYear.set(Calendar.DAY_OF_MONTH, 31);
            int days = endOfYear.get(Calendar.DAY_OF_YEAR) - c1.get(Calendar.DAY_OF_YEAR);
            for (int i=1;i <years;i++) {
                endOfYear.add(Calendar.YEAR, 1);
                days += endOfYear.get(Calendar.DAY_OF_YEAR);
            }
            days += c2.get(Calendar.DAY_OF_YEAR);
            return days;
        }
    }

Side note: The first example is slightly slower then the second, but if it's only for small differences it's neglectible.

I calculated this same way but I used dayDiff as long not as integer. So try it also, don't cast it. It should work fine.

The problem here is that you think both columns are DATE columns, where at least one of the two is really a TIMESTAMP column. When you extract one date from another, you get a NUMBER. But when you extract a date from a timestamp, or vice versa, you get an INTERVAL.

An example

A table with a DATE and a TIMESTAMP:

SQL> create table mytable (close_date,create_date)
  2  as
  3  select date '2010-11-01', systimestamp from dual union all
  4  select date '2010-11-11', systimestamp from dual union all
  5  select date '2010-12-01', systimestamp from dual
  6  /

Table created.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLOSE_DATE                                         DATE
 CREATE_DATE                                        TIMESTAMP(6) WITH TIME ZONE

Extract a TIMESTAMP from a DATE column, leads to an INTERVAL:

SQL> select close_date - create_date
  2    from mytable
  3  /

CLOSE_DATE-CREATE_DATE
---------------------------------------------------------------------------
+000000130 11:20:11.672623
+000000140 11:20:11.672623
+000000160 11:20:11.672623

3 rows selected.

And there is no need to fiddle with TO_NUMBER's and SUBSTR's. Just use the EXTRACT function to get the component you want from an interval:

SQL> select extract(day from (close_date - create_date))
  2    from mytable
  3  /

EXTRACT(DAYFROM(CLOSE_DATE-CREATE_DATE))
----------------------------------------
                                     130
                                     140
                                     160

3 rows selected.

Regards, Rob.

And here is an example with two TIMESTAMPS and which shows that INTERVAL's are truncated, not rounded:

SQL> create table mytable (close_date,create_date)
  2  as
  3  select to_timestamp('2010-11-01','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '6' hour from dual union all
  4  select to_timestamp('2010-11-11','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '12' hour from dual union all
  5  select to_timestamp('2010-12-01','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '18' hour from dual
  6  /

Table created.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CLOSE_DATE                                         TIMESTAMP(9)
 CREATE_DATE                                        DATE

SQL> select close_date - create_date
  2    from mytable
  3  /

CLOSE_DATE-CREATE_DATE
---------------------------------------------------------------------------
+000000130 18:00:00.000000000
+000000140 12:00:00.000000000
+000000160 06:00:00.000000000

3 rows selected.

SQL> select extract(day from (close_date - create_date))
  2    from mytable
  3  /

EXTRACT(DAYFROM(CLOSE_DATE-CREATE_DATE))
----------------------------------------
                                     130
                                     140
                                     160

3 rows selected.