SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-47
Examples of DIFF1
•
Retrieve the difference between the TS column in the current row and the TS
column in the previous row:
SELECT DIFF1 (TS) AS DIFF1_TS
FROM mining.seqfcn
SEQUENCE BY TS;
DIFF1_TS
--------------------
?
30002620.000000
134157861.000000
168588029.000000
114055223.000000
--- 5 row(s) selected.
Note that the results are expressed as the number of seconds. For example, the
difference between TIMESTAMP '1951-02-15 14:35:49' and TIMESTAMP '1950-
03-05 08:32:09' is approximately 347 days. The difference between TIMESTAMP
'1955-05-18 08:40:10' and TIMESTAMP '1951-02-15 14:35:49' is approximately 4
years and 3 months, and so on.
•
This query retrieves the difference in consecutive values in I1 divided by the
difference in consecutive values in TS:
SELECT DIFF1 (I1,TS) AS DIFF1_I1TS
FROM mining.seqfcn
SEQUENCE BY TS;
DIFF1_I1TS
-------------------
?
.0007319
-.0000679
-.0000857
.0000646
--- 5 row(s) selected.
Note that the results are equivalent to the quotient of the results from the two
preceding examples. For example, in the second row of the output of this example,
0.0007319 is equal to 21959 divided by 30002620.