Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Cool Unanswered: SQl Query to find time diff in minutes from Timestmp

    hi can anyone tell me how to find time diffe in minutes from timestamp ...

    i tried below sql queries but these are not retrieving correct diff in minutes


    SELECT TRUNC((86400*(CURRENT TIMESTAMP - ('2011-06-18-05.03.41.300585')))/60)
    -60*(TRUNC(((86400*(CURRENT TIMESTAMP - ('2011-06-18-05.03.41.300585')))/60)
    /60))
    FROM SYSIBM.SYSDUMMY1


    SELECT MINUTE(CURRENT TIMESTAMP) - MINUTE('2011-06-15-02.01.56.178837')
    FROM SYSIBM.SYSDUMMY1




    SELECT CURRENT TIMESTAMP, (CURRENT TIMESTAMP - '2011-06-20-04.25.39.919005')/60

    FROM SYSIBM.SYSDUMMY1;



    05 WS-TIME-DIFF PIC X(04).
    05 BDR-BKT-REQ-TIMESTMP PIC X(26) VALUE '2011-06-15-02.01.56.178837' .
    EXEC SQL
    SET :WS-TIME-DIFF = MINUTE(CURRENT TIMESTAMP) - MINUTE(BDR-BKT-REQ-TIMESTMP)
    END-EXEC



    SO IF ANYONE KNEW any other query TO FIND TIME DIFFERENCE FROM TIMESTAMP IN MINUTES ...PLS TELL ME AS SOON AS POSSIBLE

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    scalar function TIMESTAMPDIFF
    hope i was quick enough with the answer!!!

    you syntax is very poor.
    this would also work:
    MINUTE(CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP - 4 MINUTES))
    Last edited by dbzTHEdinosaur; 06-22-11 at 06:45.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    are you attempting to determine the interval in minutes between two timestamps
    that my be days apart?
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example of expression may be...
    ( DAYS(to_timestamp) - DAYS(from_timestamp) ) * 1440 /* 1440 = 24 Hr x 60 Min */
    + ( MIDNIGHT_SECONDS(to_timestamp) - MIDNIGHT_SECONDS(from_timestamp) ) / 60

Posting Permissions

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