1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Time difference between two dates

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Jan 28, 2010.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Good morning,

    I have two fields which are date fields and have the following format:

    03-MAR-09 10.36.36.791000000 AM

    How can I write an SQL statement to show that thier difference should be less than or equal to 48 hours like:

    Afield - Bfield <= 48 hours


    Thanks Respecfully,
    Farshad
     
  2. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    Re: Question about time difference please?

    Convert them to Oracle dates. When you subtract the two, you can use this kind of a conversion:

    select ((sysdate - (sysdate -120/1440))*86400)/3600 from dual

    This will give a result in hours (there are 86400 seconds in a day and 3600 in an hour). The example here will give a result of 2 hours.
     
    Farshad Javadi likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Question about time difference please?

    Those are timestamp columns and the 'format' you see is simply a display format which does not affect how these timestamps are stored internally. Noting that you'll need to perform some conversions on those timestamp values to get your subtraction to return the value expected:

    Code (SQL):
    SQL> CREATE TABLE ttest(
      2  tt1 TIMESTAMP,
      3  tt2 TIMESTAMP
      4  );

    TABLE created.

    SQL>
    SQL> INSERT INTO ttest(tt1) VALUES(systimestamp);

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL> UPDATE ttest SET tt2 = to_timestamp(sysdate+2);

    1 ROW updated.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT tt1, tt2 FROM ttest;

    TT1
    ---------------------------------------------------------------------------
    TT2
    ---------------------------------------------------------------------------
    28-JAN-10 12.14.54.846272 PM
    30-JAN-10 12.00.00.000000 AM


    SQL>
    SQL> SELECT (to_date(TO_CHAR(tt2, 'DD-MON-RR HH24:MI:SS'), 'Dd-MON-RR HH24:MI:SS') - to_date(TO_CHAR(tt1, 'DD-MON-RR HH24:MI:SS'), 'Dd-MON-RR HH24:MI:SS'))*24 hours_diff
      2  FROM ttest;

    HOURS_DIFF
    ----------
    35.7516667

    SQL>
     
     
    Farshad Javadi likes this.
  4. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Re: Question about time difference please?

    Zargon,

    Thanks Very Very Much for your excellent example. I Really Really appreciate it.

    Farshad
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Question about time difference please?

    Another workaround using CAST Function ( instead of Converting timestamp to varchar and then to Date)

    Code (SQL):

    SQL> CREATE TABLE TTEST(
      2                      TT1 TIMESTAMP,
      3                      TT2 TIMESTAMP
      4                  );

    TABLE created.

    SQL> INSERT INTO TTEST(tt1) VALUES(systimestamp);

    1 ROW created.

    SQL> UPDATE TTEST SET tt2 = TO_TIMESTAMP(SYSDATE+2);

    1 ROW updated.

    SQL> SELECT * FROM TTEST;

    TT1
    ---------------------------------------------------------------------------
    TT2
    ---------------------------------------------------------------------------
    29-JAN-10 11.55.19.532075 AM
    31-JAN-10 11.55.24.000000 AM


    SQL> SELECT (CAST(TT2 AS DATE) - CAST(tt1 AS DATE)) day_diff  FROM TTEST;

      DAY_DIFF
    ----------
     2.0000463

    SQL> SELECT (CAST(TT2 AS DATE) - CAST(tt1 AS DATE))*24 hour_diff  FROM TTEST;

     HOUR_DIFF
    ----------
    48.0011111

    SQL>

     
     
    kiran.marla and Farshad Javadi like this.
  6. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Re: Question about time difference please?

    Dear Rajavu,

    You are really genius. Your answer is short and coorect.

    I really appreciate you from the bottom of my heart.



    Thanks Very Much,
    Farshad
     
  7. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Question about time difference please?

    yes this is nice soln. by seeing this i memorize one example.

    take emp table example. their hiredates are some in 1981's etc.

    my requirement is that from hiredate to sysdate ,
    ex:the exp should display as
    24 years , 8 months and 24 days.
    i get years and months but stopped with days counting . i tried with hiredate - sysdate yeilding wrong ans. but this is not correct so. how can i achieve the days also.
     
    Farshad Javadi likes this.
  8. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Question about time difference please?

    raj, how can i achieve the same with using cast if datatype is date type instead of timestamp?
     
    Farshad Javadi likes this.
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Question about time difference please?

    You do not need CAST to make a DATE column a DATE datatype.

    HIREDATE - SYSDATE gives you the total number of days between the two dates. Since you have the years and months calculated (you did not show us how you generated those values and, really, you should) you have enough information to extract the number of days less than a month which remain. One example of how to do this follows:

    Code (SQL):
    SQL> WITH ttl_months AS(
      2          SELECT
      3          months_between(sysdate, to_date('03-JUN-81', 'DD-MON-RR')) months
      4          FROM dual
      5  )
      6  SELECT trunc(months/12) years,
      7         trunc(((months/12)-trunc(months/12))*12) mths,
      8         trunc(((((months/12)-trunc(months/12))*12) -
      9         trunc(((months/12)-trunc(months/12))*12))*30) days
     10  FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
            28          7         25

    SQL>
     
    Farshad Javadi and kiran.marla like this.
  10. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi, zargon. thanks a lot. what ttl_months will do exactly. i dint get exactly. can u please explain the whole the code exactly
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The subquery uses the MONTHS_BETWEEN built-in function to determine the total number of months between the two dates. It aliases that value to a new column name that can be used to simplify the remaining calculations. Reading the documentation on such functions (MONTHS_BETWEEN) would be an excellent way to learn about such functionality provided by Oracle.
     
    kiran.marla likes this.
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Question about time difference please?


    Not a perfect SQL command to find the Exact days between the two dates. It fails in the following cases. Numeber of Days between 03-FEB-2010 and

    a. 01-MAR-2010 is 0 (Y) 0 (M) 28 (D)
    b. 02-MAR-2010 is 0 (Y) 0 (M) 29 (D)
    c. 03-MAR-2010 is 0 (Y) 0 (M) 29 (D)
    d. 04-MAR-2010 is 0 (Y) 1 (M) 0(D)
    e. 05-MAR-2010 is 0 (Y) 0 (M) 1 (D)
    f. 06-MAR-2010 is 0 (Y) 0 (M) 2 (D)

    Code (SQL):


    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('01-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          0         28

    SQL>
    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('02-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          0         29

    SQL>
    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('03-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          0         29

    SQL>
    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('04-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          1          0

    SQL>
    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('05-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          1          1

    SQL>
    SQL> WITH ttl_months AS(
      2              SELECT
      3              months_between(to_date('06-MAR-2010', 'DD-MON-RRRR'), to_date('03-FEB-2010', 'DD-MON-RRRR')) months
      4              FROM dual
      5      )
      6      SELECT trunc(months/12) years,
      7             trunc(((months/12)-trunc(months/12))*12) mths,
      8             trunc(((((months/12)-trunc(months/12))*12) -
      9             trunc(((months/12)-trunc(months/12))*12))*30) days
     10     FROM ttl_months;

         YEARS       MTHS       DAYS
    ---------- ---------- ----------
             0          1          2

    SQL>

     
     
  13. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The following query may help you.

    Code (SQL):

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('01-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
      6   ( SELECT end_date ,start_date ,
      7             months_between( end_date, start_date ) monbet
      8     FROM TT1 )
      9  SELECT TRUNC(monbet/12) YEARS,
     10         MOD( TRUNC(monbet),12) MONTHS,
     11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          0         26

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('02-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
      6   ( SELECT end_date ,start_date ,
      7             months_between( end_date, start_date ) monbet
      8     FROM TT1 )
      9  SELECT TRUNC(monbet/12) YEARS,
     10         MOD( TRUNC(monbet),12) MONTHS,
     11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          0         27

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('03-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
     ( SELECT end_date ,start_date ,
      6    7             months_between( end_date, start_date ) monbet
       FROM TT1 )
      8    9  SELECT TRUNC(monbet/12) YEARS,
           MOD( TRUNC(monbet),12) MONTHS,
     10   11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          1          0

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('04-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
      6   ( SELECT end_date ,start_date ,
      7             months_between( end_date, start_date ) monbet
      8     FROM TT1 )
      9  SELECT TRUNC(monbet/12) YEARS,
     10         MOD( TRUNC(monbet),12) MONTHS,
     11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          1          1

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('05-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
      6   ( SELECT end_date ,start_date ,
      7             months_between( end_date, start_date ) monbet
      8     FROM TT1 )
      9  SELECT TRUNC(monbet/12) YEARS,
     10         MOD( TRUNC(monbet),12) MONTHS,
     11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          1          2

    SQL>
    SQL> WITH TT1 AS
      2   ( SELECT to_date('06-MAR-2010', 'DD-MON-RRRR') end_date,
      3            to_date('03-FEB-2010', 'DD-MON-RRRR') start_date
      4     FROM DUAL ),
      5  PARAM AS
      6   ( SELECT end_date ,start_date ,
      7             months_between( end_date, start_date ) monbet
      8     FROM TT1 )
      9  SELECT TRUNC(monbet/12) YEARS,
     10         MOD( TRUNC(monbet),12) MONTHS,
     11         end_date - ADD_MONTHS(start_date,trunc( monbet )) DAYS
     12  FROM PARAM;

         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             0          1          3

    SQL>

     
     
    kiran.marla likes this.
  14. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    appreciation for ur work
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Question about time difference please?

    I never claimed it was the 'perfect SQL'; I stated it was an example and nothing more.
     
  16. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Sorry Zargon,
    No Offence is meant. I hope you take it as a healthy argument.