Cognos Analytics

 View Only

  • 1.  Calculate minutes between 2 dates/times

    Posted Mon September 20, 2021 09:04 AM

    Hi all, 

    New here with no formal Cognos training. Trying to figure stuff out on my own and it is a real challenge sometimes. 

    This question seems so easy but I can find any answers that are helpful.

    I am trying to find the time in minutes only between 2 dates/times such as September 1, 2021 1:30 pm and September 1, 2021 3:00 pm. I want the see 90 instead of 1 hour 30 minutes and I can't figure it out. 

    This is my formula that gives me 1 hour 30 minutes: [Inspection].[Inspection Fact].[Inspection End Datetime]-[Inspection].[Inspection Fact].[Inspection Start Datetime]

    I tried changing the format but it never changes. 

    Any help would be greatly appreciated. 

    C



    ------------------------------
    Chad King
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Calculate minutes between 2 dates/times

    Posted Mon September 20, 2021 10:05 AM
    When you do any time math, you end up with an Interval datatype. You can use extract to get various details from there. 

    Try this:
    extract(minute,[interval]) + (extract(hour,[interval])*60) + (extract(day,[interval])*1440)

    where the [interval] data item holds your expression [Inspection].[Inspection Fact].[Inspection End Datetime]-[Inspection].[Inspection Fact].[Inspection Start Datetime]

    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 3.  RE: Calculate minutes between 2 dates/times

    Posted Mon September 20, 2021 06:58 PM

    _minutes_between ( x, y ) will return an integer value where x and y are either a timestamp, timestamp with TZ, time or time with TZ

    x  - y will return an interval type



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 4.  RE: Calculate minutes between 2 dates/times

    Posted Tue September 21, 2021 12:57 PM
    Hi Chad, 

    the formula to calculate the seconds between 2 timestamps is

    ((extract(second ; [date2] )) - (extract(second ; [date1] ))) +
    ((extract(minute ; [date2] )) - (extract(minute ; [date1] )))*60 +
    ((extract(hour ; [date2] )) - (extract(hour ; [date1] )))*60*60 +
    (_days_between( [date2] ; [date1] ))*24*60*60

    Cheers
    Christian

    ------------------------------
    Christian Seiser
    ------------------------------

    Original Message