Clarity

Expand all | Collapse all

Way to get a Date Diff to show in Days/hours/Mins?

  • 1.  Way to get a Date Diff to show in Days/hours/Mins?

    Posted 08-30-2018 04:24 PM

    I have read every single post I can find on calculated fields and some on dates, but I have yet to find a firm solution.  Is the below scenario possible? If so, it is a gel script to populate the field, can calculated fields do it (so far I have no been successful in this)? Do I need multiple fields to represent it?

     

    Scenario:

    We have two dates. I want to be able to calculate the difference in those dates; however, the response could be days, hours, or only minutes of a combination. This must show in another field, so that it can be used in views/reports as well as on the screen.

     

    Example:

    System Down  9/18/2018  @ 10:00 AM

    System Up  9/20/2018 @  11:30 AM

    Result : total downtime is 2 Days, 1 hr and 30 mins

     

    OR

    some records may be System down 9/18/2018 @ 10 AM  system up at 9/18/2018 10:30 am. Total downtime is 30 mins

     

    So there are variations.

     

     

    Is this even feasible? Oddly, when I try a calculated field, it actually accepts/validates unit as all three (Day, Hour, Minute) but no matter what combination I used, it only populates the first unit value listed. Can the calculated expression be manually entered to make this work?  Would a gel script/process do this  (I am not skilled at that, but at least I would like to understand the feasibility of it)

     

    Version 15.4.1

    SQL

    This is a custom subobject on the project object.

     

    thanks!

    Lynn



  • 2.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 08-31-2018 02:07 AM

    If I recall it correctly Datediff returns one number which can be eg days, hours, minutes, etc.

    Are you trying to save this number or are you trying to save three different numbers with the units?

    A field can only take one value. That is the number calculated with Datediff or if you want to save the number of days, hours, minutes with the units you would have to convert those to one single string and save the string.

     

    A gel process could do all of that.

    My approach would be first to XOG out an example then write the input file for XOG write based on that and then a gel script for a process which does the calculation and makes the XOG.



  • 3.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 08-31-2018 03:04 PM

    What is the data type of the field you have?

     

    What  I have had in a portlet query is

     

    SELECT
    ...

    hrs,
    mins,
    secs,
    ...

     

    FROM

     

    ...

    convert(varchar,(datepart(hour,(getdate()-cmn_sessions.created_date)))) hrs,
    convert(varchar,(datepart(minute,(getdate()-cmn_sessions.created_date)))) mins,
    convert(varchar,(datepart(second,(getdate()-cmn_sessions.created_date)))) secs,

    ....

     

    Those values of the three fields and their units also could be inserted into one single string field.



  • 4.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 08-31-2018 04:24 PM

    For both questions:

    That is what I am asking.. using a Calculated field only returns one number ( with specified digits) even though it validates multiple units   seems wrong..

     

    the two attributes on the project are date/time  so it shows like the example I provided (both date  and time)

    What I am trying to get is when the user puts in the down date and time and the up date and times, SAVES.. the system to show on the screen the total difference in those dates and times.

    Ideally, I would like one field to show something like 1D 1H 30 mins, but knowing that is a wishful thinking, I am wondering if I need 3 fields such as field one = day = 1,, 2 = Hour=1 and 3= Minute = 30; however, the calculated field only shows a number. It won't show what the unit type is.. so I would have to rely on field labels.

     

    knowing that can get ugly, especially if they need to report upon or export data, I am asking what tricks could a gel script do to populate this desired field (again, desire a single field) but the user would need to  "easily" tell it is in DD/HH/Min format. 

    Sounds like we need to investigate gel capabilities.. thank you  (I am not a gel writer, so I can't offer more than that  ) 



  • 5.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 09-01-2018 03:48 PM

    I looked into it some more

    It looks to me that it cannot be done in a calculated field which is of type text/string as it is of type Concat and does not seem to accept any string manipulation nor the object instance

     

    Calculated attribute of type date seems to be only for adding a constant to a date.

     

    So still wondering which data type you had and what kind of calculation.

     

    Dave's "How to put any dynamically created value" would do the trick for a property view, but it does not work in a list view.

     

    To me a gel process with the calculation done with SQL and the value XOGed in maybe as an autostart process sounds most attractive right now.



  • 6.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Broadcom Employee
    Posted 09-03-2018 01:21 AM

    Hi Lynn,

     

    I added below statement in NSQL like as below.  (I use Oracle)

     

    @SELECT:DIM_PROP:USER_DEF:IMPLIED:XXXXXXX:System_Up - System_Down:RESULT@

    The number of days between System_Up and System_Down is stored in RESULT column.

     

    If System_Down is "2018 Sep 1st" and "System_Up is 2018 Sep 4th",  RESULT shows 3 (days).

    If System_Down is "2018 Sep 1st 10:00" and "System_Up is 2018 Sep 4th 22:00",  RESULT shows 3.5 (days).

     

    Regards,

    Shoichi



  • 7.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 09-04-2018 09:55 PM

    Thank you.. I will look into it some more -- appreciate it!



  • 8.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 09-06-2018 11:42 AM

    You will need to get your SQL dates into Java Dates but this should produce your results string.

     

    <?xml version="1.0" encoding="utf-8"?>
    <gel:script
         xmlns:core="jelly:core"
         xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
         xmlns:xsd="http://www.w3.org/2001/XMLSchema"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


         <gel:log>Calculate Downtime Script</gel:log>

         <!-- Set some test dates -->
         <core:new className="java.text.SimpleDateFormat" var="formatter">
              <core:arg type="java.lang.String" value="yyyy-MM-dd HH:mm:ss" />
         </core:new>

         <core:set var="dateStart" value='${formatter.parse("2018-07-09 12:12:12")}' />
         <core:set var="dateEnd" value='${formatter.parse("2018-09-01 18:25:06")}' />

         <gel:log>${dateStart.toString()} | ${dateEnd.toString()}</gel:log>

         <!-- Calclate the days/hours/minutes/seconds -->
         <core:set var="different" value="${dateEnd.getTime() - dateStart.getTime()}" />
         <core:set var="diffDays" value="${different / (24 * 60 * 60 * 1000)}" />
         <core:set var="different" value="${different % (24 * 60 * 60 * 1000)}" />
         <core:set var="diffHours" value="${different / (60 * 60 * 1000)}" />
         <core:set var="different" value="${different % (60 * 60 * 1000)}" />
         <core:set var="diffMinutes" value="${different / (60 * 1000)}" />
         <core:set var="different" value="${different % (60 * 1000)}" />
         <core:set var="diffSeconds" value="${different / 1000}" />

         <gel:log>Days ${diffDays.intValue()} | Hours ${diffHours.intValue()} |  Minutes ${diffMinutes.intValue()} |  Seconds ${diffSeconds.intValue()}</gel:log>

         <gel:log>Calculate Downtime Script</gel:log>

    </gel:script>

     

     

    V/r,

    Gene



  • 9.  Re: Way to get a Date Diff to show in Days/hours/Mins?

    Posted 09-06-2018 02:54 PM

    Wow. .thank you so much!

     

    I will give it a shot.. appreciate the lift to really get it going!