Oracle和Java中日期的日期差 [英] 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.

我有一个可计算日期差(以天为单位)的应用程序-在搜索掩码和详细信息掩码中.在第一个掩码中，我使用几天来筛选具有持续时间搜索条件的记录(搜索掩码")，并使用SQL查询来做到这一点:

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

在这种特殊情况下，我尝试找出持续时间为140天的所有记录.

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

在第二个遮罩(详细信息)中，我显示记录的详细信息，包括其持续时间.这是通过以下Java代码完成的:

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);
}

我的问题是，当我搜索时，我发现了一些确实符合搜索条件的记录.例如，我找到了4条记录，所有记录都有140天的持续时间.根据Oracle的说法.但是，当我为其中的一些打开详细信息"遮罩时，例如，我得到的时间为139天.因此，Java和Oracle以某种方式以不同的方式计算日期差.似乎在Oracle中已经进行了一些四舍五入，但是我找不到发生这种情况的地方.因此，任何建议都将有所帮助.谢谢！

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!

问候， 阿拉玛克

推荐答案

日期可以相同，但时间可以不同.毫秒计算得出的结果为139天. (java)

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.

类似

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++;
   }}

或

/**
     *
     * @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.

这篇关于Oracle和Java中日期的日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！