How to Truncate Timestamp (DATE_TRUNC) in ksqlDB

In ksqlDB, the DATE_TRUNC() function is not available. Instead, you need to use FORMAT_TIMESTAMP() to truncate a part of a timestamp value.

Below are examples of truncating timestamps to month, day, hour, etc.

SELECT 
  FORMAT_TIMESTAMP(registered_at, 'yyyy-01-01'), -- year
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-01'), -- month
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-dd'), -- day
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-dd 00:00:00.000'), -- day with time
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-dd HH:00:00.000'), -- hours
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-dd HH:mm:00.000'), -- minutes
  FORMAT_TIMESTAMP(registered_at, 'yyyy-MM-dd HH:mm:ss.000'), -- seconds
FROM USERS;

The full pattern is yyyy-MM-dd HH:mm:ss.SSS so you can replace that with 0s or 01s on the part you need to truncate.

Was this article helpful?