Tip: 🕐 DATEDIFF in ksqlDB

ksqlDB
1

You can calculate the difference between a timestamp and the current time using UNIX_TIMESTAMP() thus:

SELECT TIMESTAMPTOSTRING(ORDERTIME,       'yyyy-MM-dd HH:mm:ss','Europe/London')    AS ORDER_TIME,
       TIMESTAMPTOSTRING(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss','Europe/London')    AS NOW_TS,
       (UNIX_TIMESTAMP() - ORDERTIME)                                               AS DIFF_MS,
       (CAST(UNIX_TIMESTAMP() AS DOUBLE) - CAST(ORDERTIME AS DOUBLE))/1000/60/60    AS DIFF_HOURS,
       (CAST(UNIX_TIMESTAMP() AS DOUBLE) - CAST(ORDERTIME AS DOUBLE))/1000/60/60/24 AS DIFF_DAYS
       FROM ORDERS
EMIT CHANGES LIMIT 5;

+--------------------+---------------------+------------+-------------+-----------+
|ORDER_TIME          |NOW_TS               |DIFF_MS     |DIFF_HOURS   |DIFF_DAYS  |
+--------------------+---------------------+------------+-------------+-----------+
|2019-06-09 19:59:14 |2021-02-11 13:22:48  |52943014365 |14706.39287  |612.766369 |
|2019-06-09 03:37:49 |2021-02-11 13:22:48  |53001898589 |14722.74960  |613.447900 |
|2019-06-09 11:35:57 |2021-02-11 13:22:48  |52973211434 |14714.78095  |613.115873 |
|2019-06-09 02:47:28 |2021-02-11 13:22:48  |53004919692 |14723.58880  |613.482866 |
|2019-06-09 23:03:33 |2021-02-11 13:22:48  |52931955521 |14703.32097  |612.638374 |
1 Like