ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Time Difference

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Time Difference

    Hi All,

    I have two time fields, in decimal data type.

    Eg Starttime = 192020 Endtime = 222020.

    I have to find out the difference between these two times and the result should be in Minutes?

    How can i achive this?
    Regards,
    Vinothkumar S.

  • #2
    Re: Time Difference
    1. Convert the numeric times into realtimes times
    2. Use the SQL scalar function MIDNIGHT_SECONDS to convert the real times into seconds since midnight.
    3. Subtract the seconds from each other
    4. Divide the result through 60.


    Writing a function with RPG and register it as external UDF would be an easier way.

    Birgitta

    Comment


    • #3
      Re: Time Difference

      As Birgitta says, one option is to write an ILE RPG program that uses this: %DIFF (Difference between two date, time or timestamp values). Then register is as a UDF.
      "Time passes, but sometimes it beats the <crap> out of you as it goes."

      Comment


      • #4
        Re: Time Difference

        Hi,

        As per input from birgitta i have created one UDF using SQL. Please refer the code.

        Code:
        CREATE FUNCTION TIMEDIFF (josttm Decimal(6, 0),
         joentm Decimal(6, 0))
         Returns Decimal(6, 0)
         Language SQL
         NOT DETERMINISTIC
         READS SQL DATA
         RETURNS NULL ON NULL INPUT
         Begin
          Declare STTM TIME;
          Declare ENTM TIME;
          Declare Diff Decimal(6, 0);
          Set STTM = TIME(
            SUBSTRING(DIGITS(JOSTTM) from 1 for 2)
            concat ':' concat
            SUBSTRING(DIGITS(JOSTTM) from 3 for 2) concat ':' concat
            SUBSTRING(DIGITS(JOSTTM) from 5 for 2));
        
          Set ENTM = TIME(
            SUBSTRING(DIGITS(JOENTM) from 1 for 2)
            concat ':' concat
            SUBSTRING(DIGITS(JOENTM) from 3 for 2) concat ':' concat
            SUBSTRING(DIGITS(JOENTM) from 5 for 2));
        
           Select Midnight_seconds(Sttm) - Midnight_seconds(
               Entm) into Diff from sysibm/sysdummy1;
        Return Diff/60;
        End;
        Regards,
        Vinothkumar S.

        Comment


        • #5
          Re: Time Difference

          Why are you using a select-statement to substract the times?
          For each select statement an query optimization must be performed, which is rather time consuming.
          If you are already on V5R4 or higher a SET-Statement gets directly executed.

          PHP Code:
          Return (Midnight_Seconds(Sttm) - Midnight_Seconds(EntM))/60
          Birgitta

          Comment

          Working...
          X