Results 1 to 6 of 6

Thread: Timestampdiff

  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Timestampdiff

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    Hello all.

    I'm in the process of migrating our software from SQL SERVER to ORACLE and have a question for you guys.

    Now SQL SERVER supports the SQL extensions functions {fn ...} but it seems Oracle does not.

    I've looked through numerous articles regarding TIMESTAMPDIFF and am unclear as to the best way to do what I want in Oracle.

    First thing - Oracle does not support TIMESTAMPDIFF - correct? - I say this as I have seen a few Oracle articles flying around that do actually make reference to TIMESTAMPDIFF???

    Here is my example: (vendortest.vt_datetime is a DATETIME in SQLSERVER, TIMESTAMP in ORACLE)

    vt_datetime values:
    24-OCT-2009 00.00.00.000000000
    25-OCT-2008 00.00.00.000000000
    26-OCT-2007 00.00.00.000000000

    SQL SERVER:
    select GETDATE() as now, vt_datetime,
    {fn TIMESTAMPDIFF(SQL_TSI_DAY, GETDATE(), vt_datetime)} as days,
    {fn TIMESTAMPDIFF(SQL_TSI_MONTH, GETDATE(), vt_datetime)} as months,
    {fn TIMESTAMPDIFF(SQL_TSI_YEAR, GETDATE(), vt_datetime)} as years
    FROM vendortest

    Gives:
    now vt_datetime days months years
    ----------------------- ----------------------- ----------- ----------- -----------
    2009-10-30 09:55:55.027 2009-10-24 00:00:00.000 -6 0 0
    2009-10-30 09:55:55.027 2008-10-25 00:00:00.000 -370 -12 -1
    2009-10-30 09:55:55.027 2007-10-26 00:00:00.000 -735 -24 -2

    ORACLE:
    ?

    Any help/pointers regarding TIMESTAMPDIFF in Oracle would be greatly appreciated.

    regards,

    Andy

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    You don't need a function, just subtract the two dates:

    Code:
    SELECT current_timestamp - vt_datetime
    FROM vendortest

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Thanks for your reply shammat.

    I've jut got round the issue with the below SQL:

    SELECT sysdate, vt_datetime,
    trunc(to_number(substr((sysdate-vt_datetime),1,instr(sysdate-vt_datetime,' ')))) * -1 days,
    round(months_between(sysdate, vt_datetime)) * -1 as months,
    (to_number(round(months_between(sysdate, vt_datetime))) / 12) * -1 as years
    from vendortest

    Which gives me the required results:

    SYSDATE VT_DATETIME DAYS MONTHS YEARS
    ------------------------- ------------------------- ---------------------- ---------------------- ----------------------
    30-OCT-09 24-OCT-09 00.00.00.000000000 -6 0 0
    30-OCT-09 25-OCT-08 00.00.00.000000000 -370 -12 -1
    30-OCT-09 26-OCT-07 00.00.00.000000000 -735 -24 -2


    Cheers.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    That doesn't look very robust. You are relying on implicit casting of a timestamp column which might yield different results depending on the NLS settings of the client (30-OCT-09 vs. 2009-10-30 vs. 30-OKT-2009)

    Maybe if you told us the underlying problem you are trying to solve, we can help you with a better (i.e. more readable and robust) statement.

  5. #5
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    My underlying problem is as stated in my initial post.
    i.e. Mimicking the ODBC SQL extensions function {fn TIMESTAMPDIFF(...)} that Oracle does not support.

    Basically I want to:
    1. Show the number of days difference between 2 dates (timestamp columns)
    2. Show the number of months difference between 2 dates (timestamp columns)
    3. Show the number of years difference between 2 dates (timestamp columns)

    I am in agreement with you in that my solution is not elegant, but it produces the results I want.

    Any better solutions though are most appreciated.

    regards,

    Andy

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Code:
    select extract(day from vt_datetime - current_timestamp) as days,
           extract(month from (vt_datetime - current_timestamp) year to month) as months,
           extract(year from (vt_datetime - current_timestamp) year to month) as years
    from vendortest
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •