This discussion is archived
7 Replies Latest reply: Jun 11, 2013 5:35 AM by GaryMyers RSS

Problem between Dates operations

1011943 Newbie
Currently Being Moderated
Hi!

Hope everyone is well. I need some help, I'm trying to obtain the time differences between two hours.

I have the next Inputs:

Start_Time: 2014-03-23 20:03:00
End_Time: 2014-03-23 21:03:15

If you can see, there is an hour in time difference, but when I execute the next query, my result is diferent.

SELECT End_Time - Start_Time FROM DUAL;

The result is: 0.0772569444444444444444444444444444444444

This is definetaly wrong, if I do the same operation in EXCE the result is: 0.041840278 (Correct).

Then I multiply that by 24 hrs

SELECT End_Time - Start_Time * 24 FROM DUAL;

And the results are in here:
Oracle: 1.85416666666666666666666666666666666667
Excel: 1.004166667

So, Excel value is the one I'm trying to reach.

In a different computer, toad gives the right value. I'm using SQL Developer.

Can anyone help me?

Thank you!
  • 1. Re: Problem between Dates operations
    rp0428 Guru
    Currently Being Moderated
    >
    Hope everyone is well. I need some help, I'm trying to obtain the time differences between two hours.

    I have the next Inputs:

    Start_Time: 2014-03-23 20:03:00
    End_Time: 2014-03-23 21:03:15

    If you can see, there is an hour in time difference, but when I execute the next query, my result is diferent.

    SELECT End_Time - Start_Time FROM DUAL;

    The result is: 0.0772569444444444444444444444444444444444

    This is definetaly wrong, if I do the same operation in EXCE the result is: 0.041840278 (Correct).

    Then I multiply that by 24 hrs

    SELECT End_Time - Start_Time * 24 FROM DUAL;

    And the results are in here:
    Oracle: 1.85416666666666666666666666666666666667
    Excel: 1.004166667

    So, Excel value is the one I'm trying to reach.

    In a different computer, toad gives the right value. I'm using SQL Developer.
    >
    What version and platform of sql developer are you using? Are you sure you are using the same data for each of those queries?

    Works just fine for me in sql developer Version 3.0.02
    with q as (select to_date('2014-03-23 20:03:00', 'yyyy-mm-dd hh24:mi:ss') Start_Time,
                      to_date('2014-03-23 21:03:15', 'yyyy-mm-dd hh24:mi:ss') End_Time from dual)
    select start_time, end_time, end_time - start_time, (end_time - start_time) * 24 days from q
    
    START_TIME                END_TIME                  END_TIME-START_TIME    DAYS                   
    ------------------------- ------------------------- ---------------------- ---------------------- 
    23-MAR-14                 23-MAR-14                 0.0418402777777777777777777777777777777778 1.00416666666666666666666666666666666667 
  • 2. Re: Problem between Dates operations
    MLBrown Journeyer
    Currently Being Moderated
    Works for me as well:
    select to_date('2014-03-23 20:03:00', 'YYYY-MM-DD HH24:MI:SS') -
           to_date('2014-03-23 21:03:15', 'YYYY-MM-DD HH24:MI:SS') diff
      from dual;
    
    DIFF
    --------------------------------------
    -0.04184027777777777777777777777777778
  • 3. Re: Problem between Dates operations
    GaryMyers Newbie
    Currently Being Moderated
    My bet is that you've got "MM" instead of "MI" in the format mask, so you are seeing the Months component in place of the Minutes component.
    As such, the "03" isn't the minutes and that the difference between the "something past 8pm" and "something past 9pm" values is actually 51 minutes.
  • 4. Re: Problem between Dates operations
    rp0428 Guru
    Currently Being Moderated
    >
    My bet is that you've got "MM" instead of "MI" in the format mask, so you are seeing the Months component in place of the Minutes component.
    As such, the "03" isn't the minutes and that the difference between the "something past 8pm" and "something past 9pm" values is actually 51 minutes.
    >
    Have you tested that 'bet'? Post the 'format' that OP might be using where that would be the case.

    If OP is using DATE datatypes there is no 'format' involved and if using strings you can't use the same format code twice.
    with q as (select to_date('2014-03-23 20:03:00', 'yyyy-mm-dd hh24:mm:ss') Start_Time,
                      to_date('2014-03-23 21:03:15', 'yyyy-mm-dd hh24:mm:ss') End_Time from dual)
    select start_time, end_time, end_time - start_time, (end_time - start_time) * 24 days from q
    
    ORA-01810: format code appears twice
  • 5. Re: Problem between Dates operations
    GaryMyers Newbie
    Currently Being Moderated
    Here's the test

    create table t_dt (start_date date, end_date date);

    insert into t_dt values (timestamp '2014-03-23 20:01:00', null);
    update t_dt set end_date = start_date + 0.0772569444444444444444444444444444444444;

    <pre>
    select to_char(start_date,'yyyy-mm-dd hh24:mm:ss') start_dt, to_char(end_date,'yyyy-mm-dd hh24:mm:ss') end_dt,
    d_date - start_date) diff
    2 from t_dt;

    START_DT END_DT DIFF
    ------------------- ------------------- ----------------------------------------
    2014-03-23 20:03:00 2014-03-23 21:03:15 .077256944444444444444444444444444444444
    select to_char(start_date,'yyyy-mm-dd hh24:mi:ss') start_dt, to_char(end_date,'yyyy-mm-dd hh24:mi:ss') end_dt,
    d_date - start_date) diff
    2 from t_dt;

    START_DT END_DT DIFF
    ------------------- ------------------- ----------------------------------------
    2014-03-23 20:01:00 2014-03-23 21:52:15 .077256944444444444444444444444444444444

    </pre>

    Edited by: Gary Myers on 6/06/2013 08:29
  • 6. Re: Problem between Dates operations
    rp0428 Guru
    Currently Being Moderated
    >
    Here's the test

    create table t_dt (start_date date, end_date date);

    insert into t_dt values (timestamp '2014-03-23 20:01:00', null);
    update t_dt set end_date = start_date + 0.0772569444444444444444444444444444444444;
    >
    Ok - I give up. How is that 'the test'?

    1. Your 'start_date' value is NOT the same as OPs.
    2. Your 'end_date' value is NOT the same as OPs but uses the value that OP already says is wrong.
    3. Your query doesn't even use a format mask as part of the 'end_date - start_date' calculation

    OP is using strings since the query is from the DUAL table which doesn't have 'end_date' or 'start_date'. That is why I said above
    >
    If OP is using DATE datatypes there is no 'format' involved and if using strings you can't use the same format code twice.
    >
    For DATE datatypes you just query the difference; there is no format string involved. To create a DATE from a string, as in my example, you would use TO_DATE and a format string. But, as my example shows, the format string cannot use the same format code twice.

    You used TO_CHAR to 'display' data but for display purposes you can use a format code as many times as you want since you aren't creating anything but a string. That doesn't work if you are trying to create a DATE instance.
  • 7. Re: Problem between Dates operations
    GaryMyers Newbie
    Currently Being Moderated

    The OP states that he has the following values:

    "Start_Time: 2014-03-23 20:03:00 End_Time: 2014-03-23 21:03:15"

     

    Those are STRINGS. Oracle uses an internal 7-byte format for dates, and doesn't natively store them as strings. The OP is posting what he is seeing.

    The user posts that the database is telling him that the difference between the start_date and end_date is "0.077256944" (or 1 hour, 51 minutes and 15 seconds).

    The poster says that, on a different computer, using toad, the correct result is reported. That's a strong hint that the problem is likely with a client date format.


    Note that the 15 seconds is consistent with the difference between final component of the date values given.

    Note also the "03" between the 2014 and 23 components, and in both "20:03:00" and "21:03:15". I don't believe that is coincidence.

     

    The hypothesis, my "bet", is that the strings given by the user did NOT accurately represent the dates in the database, only the converted values displayed on the screen.

    His preferred date format in SQL Developer has been incorrectly set to show MM instead of MI.

    Cut and paste those strings into Excel, and it will treat them as dates but they would be different values than those in the database, and the subtraction would give a different answer.

     

    So, my test.

    insert into t_dt values (timestamp '2014-03-23 20:01:00', null);

    update t_dt set end_date = start_date + 0.0772569444444444444444444444444444444444;

    SQL> select to_char(start_date,'YYYY-MM-DD HH24:MI:SS') sd, to_char(end_date,'YYYY-MM-DD HH24:MI:SS') ed from t_dt;

    SD                  ED

    ------------------- -------------------

    2014-03-23 20:01:00 2014-03-23 21:52:15

     

    Those are the date values from the database explicitly converted into strings using a visible format mask..

    This is the same data, extracted using a format mask where the "MI" component of the time is substituted with the a repeat of the "MM" component.

    It gives the same apparently anomalous result as the OP reported.

     

    SQL> select to_char(start_date,'YYYY-MM-DD HH24:MM:SS') sd, to_char(end_date,'YYYY-MM-DD HH24:MM:SS') ed, end_date-start_date diff from t_dt;

    SD                  ED                        DIFF

    ------------------- ------------------- ----------

    2014-03-23 20:03:00 2014-03-23 21:03:15 .077256944

     

    Actually, I'd go further and guess that the start_date is actually 08:00:00 as that is a more typical start time for an operation.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points