我有一个复杂的sql文件,我在其中以秒为单位计算时间戳diff,如下所示:
timestampdiff(2, char(max(END_TS) - min(START_TS))) as ELAPSED_TIME
当差值以微秒为单位时,ELAPSED_TIME有时计算为0。如果差值以微秒为单位,那么ELAPSED_TIME永远不等于0,是否可以四舍五入到1秒?
示例:
[db2inst1@dashmpp-head-0 - Db2wh ~]$ db2 "select * from TEST_TBL"
COL1 COL2
-------------------------- --------------------------
2022-01-25-01.00.00.300000 2022-01-25-01.00.00.000000
1 record(s) selected.
[db2inst1@dashmpp-head-0 - Db2wh ~]$ db2 "select CEILING(timestampdiff(2, char(max(col1) - min(col2)))) from TEST_TBL"
1
-----------
0
1 record(s) selected.
由于差值为300000
微秒,因此等于0秒。我们能把它四舍五入到1秒吗?
这是部分if长选择查询,所以我不能使用if else。
按原样使用表达式,或者基于此表达式创建用户定义的标量函数。
WITH TEST_TBL (COL1, COL2) AS
(
VALUES
(TIMESTAMP ('2022-01-25-01.00.00.300000'), TIMESTAMP ('2022-01-25-01.00.00.000000'))
, (TIMESTAMP ('2022-01-25-01.00.00.000000'), TIMESTAMP ('2022-01-25-01.00.00.000000'))
, (TIMESTAMP ('2022-01-25-01.00.02.000000'), TIMESTAMP ('2022-01-25-01.00.00.000000'))
)
SELECT
COL1, COL2
-- the expression
, DECODE (COL1, COL2, 0, COALESCE (NULLIF (TIMESTAMPDIFF (2, CHAR (COL1 - COL2)), 0), 1)) AS DIFF
FROM TEST_TBL
COL1 | COL2 | DIFF |
---|---|---|
2022-01-25-01.00.00.300000 | 2022-01-25-25-01.00.00.000000 | <1>|
2022-01-25-01.00.00.000000 | 2022-025-01.00.00000000 | 0 |
2022-01-25-01.00.02000000 | 2022-025-01.00.00000000 | 2 |