如何在Confluent Cloud的KSQL中计算两个时间戳的毫秒级差值

KSQL中可通过将两个时间戳统一转换为毫秒精度的Unix时间整数值后相减，得到毫秒级差值，修改后的查询语句可参考如下两种写法：

写法1：直接在原查询中新增计算字段 #

SELECT
  ENTITY -> IENTITYID,
  ENTITY -> DTINSERTDATE,
  ENTITY -> DTUPDATEDATE, 
  HEADERS -> OPERATION,
  TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss.SSS') AS rowtime_formatted,
  CAST(ENTITY -> DTUPDATEDATE AS TIMESTAMP) AS UpdatedDateFROMtblEntity,
  -- 新增毫秒差值计算，外层包ABS()可返回非负差值
  ROWTIME - UNIX_TIMESTAMP_MILLIS(CAST(ENTITY -> DTUPDATEDATE AS TIMESTAMP)) AS diff_milliseconds
FROM tblEntity
EMIT CHANGES
LIMIT 10;

写法2：用子查询复用已有别名 #

如果不想重复写CAST转换逻辑，可通过子查询复用已经定义的UpdatedDateFROMtblEntity别名：

SELECT 
  *,
  ROWTIME - UNIX_TIMESTAMP_MILLIS(UpdatedDateFROMtblEntity) AS diff_milliseconds
FROM (
  SELECT
    ENTITY -> IENTITYID,
    ENTITY -> DTINSERTDATE,
    ENTITY -> DTUPDATEDATE, 
    HEADERS -> OPERATION,
    TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss.SSS') AS rowtime_formatted,
    CAST(ENTITY -> DTUPDATEDATE AS TIMESTAMP) AS UpdatedDateFROMtblEntity
  FROM tblEntity
  EMIT CHANGES
)
LIMIT 10;

相关说明 #

• KSQL内置的ROWTIME本身就是毫秒精度的Unix时间戳（BIGINT类型），不需要额外转换
• UNIX_TIMESTAMP_MILLIS()函数作用是将TIMESTAMP类型的字段转换为对应的毫秒级Unix时间整数值
• 计算结果为正代表ROWTIME晚于UpdatedDateFROMtblEntity，结果为负代表ROWTIME早于UpdatedDateFROMtblEntity

内容的提问来源于stack exchange，提问作者bala n