Forum Stats

  • 3,874,727 Users
  • 2,266,768 Discussions
  • 7,911,960 Comments

Discussions

Idea : Date functions which will give output as days, years, minutes, seconds etc...

NSK2KSN
NSK2KSN Member Posts: 602 Bronze Badge
edited May 23, 2016 7:04AM in Database Ideas - Ideas

Hi All,

This idea is to have a function for date which will take input as three parameters.

name of the function can be DIFF_DATES (DATE1 WITH TIME STAMP, DATE2 WITH TIME STAMP, OPTIONAL PARAMETER) -> If we pass input as date1 and date2 and third paramter as 'D' then the function should return output as difference between two dates in days.

below table will give a clear idea:

Parameter1Parameter2ParameterOutput
23-MAY-201622-MAY-2016DAYS1D
23-MAY-201622-APR-2016MONTHS1 Month / 30 days
23-MAY-201622-MAY-2015YEARS1 year / 12 months / 365 days
23-MAY-2016 22:10:0023-MAY-2016 22:40:15MINUTES20MINUTES 15 SECONDS

This queries were asked several times by many of the developers

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:96012348060

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5931515900346288940

Please let us know whether you would like to have support for SQL date function which will help in reducing the customized functions and also as this is the most important and used function in a day to day life. @connor

NSK2KSN
6 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited May 23, 2016 10:00AM

    Just because this is an often asked question doesn't mean that there is no answer for it yet.

    The differences between two date values can already be shown.

    But date and calendar arithmethic is not easy. That is something many developers need to understand first.

    Currently we can do

    A - B => number of days with fractionals beeing hours, minutes, seconds

    months_between (A, B )  => Number of months (=years). Fractionals are hard to correctly interpret in this case.

    The first logic uses the day-second range , the second function works in the month-year range. With some specialized and documented logic for end of month calculations.

    I don't see that yet another function would provide any significant advantage.

    William Robertson
  • NSK2KSN
    NSK2KSN Member Posts: 602 Bronze Badge

    Just because this is an often asked question doesn't mean that there is no answer for it yet.

    The differences between two date values can already be shown.

    But date and calendar arithmethic is not easy. That is something many developers need to understand first.

    Currently we can do

    A - B => number of days with fractionals beeing hours, minutes, seconds

    months_between (A, B )  => Number of months (=years). Fractionals are hard to correctly interpret in this case.

    The first logic uses the day-second range , the second function works in the month-year range. With some specialized and documented logic for end of month calculations.

    I don't see that yet another function would provide any significant advantage.

    Agreed Sven,

    Product should also support / consider what is most frequently used in implementations. at the moment every one have their own version of the details. Thought of sharing where we have one function which will give the result based on operator.

    on that front, substr and instr function will give the result (with multiple iterations, results) what regexp-substr will give, then why oracle has introduced this functions. there is a reason for it.

    It will definitely provide advantage with the native sql function in such case.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    What have already is quite powerful, so you can combine any of these to get what you want:

    select INTERVAL'20' DAY - INTERVAL'240' HOUR from dual;

    SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

    SELECT EXTRACT(DAY FROM sysdate) FROM DUAL;

    SELECT NUMTODSINTERVAL(7.251, 'HOUR') FROM DUAL;

    SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(7.251, 'HOUR')) FROM DUAL;

    SELECT EXTRACT(MINUTE FROM NUMTODSINTERVAL(7.251, 'HOUR')) FROM DUAL;

    -- eg 10Jan2016 22:43:53 +000000063 02:27:45.000000000

    select min(COMPLETION_TIME), NUMTODSINTERVAL(sysdate-min(COMPLETION_TIME),'DAY')

    from v$archived_log;

    -- nicer duration format

    -- eg 10Jan2016 22:43:53 +063 02:26:01

    select min(COMPLETION_TIME), (sysdate-min(COMPLETION_TIME)) DAY(3) TO SECOND(0)

    from v$archived_log;

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited May 24, 2016 9:21AM

    You may find it an essential function that you need every day, but I've never really come across the need for it. This is why we have user-defined functions, e.g.

    create or replace function date_diff
        ( p_date1 date
        , p_date2 date
        , p_unit varchar2 default 'DAYS' )
        return number
        deterministic
    is
    begin
        return
            case upper(nvl(p_unit,'DAYS'))
                when 'DAYS' then p_date2 - p_date1
                when 'HOURS' then (p_date2 - p_date1) * 24
                when 'MINUTES' then (p_date2 - p_date1) * 1440
                when 'SECONDS' then (p_date2 - p_date1) * 86400
                when 'MONTHS' then months_between(p_date1,p_date1)
                when 'YEARS' then months_between(p_date1,p_date1) / 12
            end;
    end date_diff;
    
  • NSK2KSN
    NSK2KSN Member Posts: 602 Bronze Badge

    You may find it an essential function that you need every day, but I've never really come across the need for it. This is why we have user-defined functions, e.g.

    create or replace function date_diff
        ( p_date1 date
        , p_date2 date
        , p_unit varchar2 default 'DAYS' )
        return number
        deterministic
    is
    begin
        return
            case upper(nvl(p_unit,'DAYS'))
                when 'DAYS' then p_date2 - p_date1
                when 'HOURS' then (p_date2 - p_date1) * 24
                when 'MINUTES' then (p_date2 - p_date1) * 1440
                when 'SECONDS' then (p_date2 - p_date1) * 86400
                when 'MONTHS' then months_between(p_date1,p_date1)
                when 'YEARS' then months_between(p_date1,p_date1) / 12
            end;
    end date_diff;
    

    Thanks William, it all depends on what requirements we are working on to encounter the situation.