ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Use of Lag and TimestampDiff

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

  • Use of Lag and TimestampDiff

    Hi all,
    I have a log table and I want to calculate the difference in microseconds between the current record and the previous record, but I'm doing something wrong, so when I try to made the timestampdiff between the timestamp of the current record and the result of lag function I receive the SqlCode : SQL0182.

    To explain me better I made this little sample:

    Code:
    Declare Global Temporary Table Testlag 
    (Datetime Timestamp, Test Varchar(50))
    ;
    
    
    Insert into TestLag
       values (current timestamp, 'TEST 1'), 
                     (current timestamp + 100 microseconds, 'TEST 2'),
                     (current timestamp + 200 microseconds, 'TEST 3');
    
    Select *
      From Testlag;
    
    Select Datetime, 
             TimestampDiff(1, char(datetime - Lag (Datetime, 1, Datetime) Ignore Nulls Over (Order By Datetime))), Test
      From Testlag;
    Could you help me?
    Many thanks.

    Bye

  • #2
    You need to explicitly cast the timestamp difference (2nd parameter in the TimestampDiff function) to CHAR(22).
    The following statement works for me:

    Code:
    Select Datetime,
           TimestampDiff(1 , Cast(Datetime - Lag(DateTime, 1, DateTime) Ignore Nulls
                                             Over (Order By Datetime)
                                  as Char(22))),
          Test
      From Testlag;
    Birgitta

    Comment


    • #3
      Hi Birgitta,

      it still giving me the same error: SQL0182

      If I do this it works:

      Code:
      SELECT Datetime,  Lag(DateTime, 1,                     
      DateTime) ignore nulls over(order by datetime), test   
      FROM Testlag
      But simply putting the function timestamp before the lag it give me the error SQL0171:

      Code:
      SELECT Datetime,  timestamp(Lag(DateTime, 1,              
      DateTime) ignore nulls over(order by datetime)), test     
      FROM Testlag ;
      Without the timestamp or timestampdiff BIF it works.

      I think there's a bug...

      Comment


      • #4
        Why are you using the TIMESTAMP function?
        Is DATETIME a Timestamp or not?

        If it is a timestamp, why you try to convert it again into a timestamp?
        If it is not a Timestamp data type, you need to convert it first (within the LAG function)
        If you want to calculate the difference between the 2 timestamps, you need the TIMESTAMDIFF function as shown in my example.

        Birgitta

        Comment


        • #5
          Hi Birgitta,

          I was trying to use the timestamp function to be sure that the result of the lag function is really a timestamp (as I read on Db2 reference, the lag function return the same data type of the first parameter), infact if I made this it works:

          Code:
          select timestamp(current timestamp) from sysibm/sysdummy1;
          I open a case in IBM I support, I think that at my ptf level (I'm at V7R3 at the last level of cumulative and ptf groups) there will be some error with the lag function, I suppose that that function really don't return a timestamp data type.
          Infact if I try :

          Code:
          Create Table Qtemp/A As
                (Select Datetime, Lag(Datetime, 1, Datetime) Over (
                          Order By Datetime
                        ) As Pippo, Test
                    From Testlag)
                With Data;
          It give me the sqlstate: 42821

          Many thanks

          Comment


          • #6
            you are right, it works only for 7.4 but on my 7.3 machine I get the same error

            Birgitta

            Comment


            • #7
              Hi, there's a test ptf then resolve the problem. It has SI70453. In the next week probably will be released.

              Bye

              Comment

              Working...
              X