Search code examples
apache-kafkaconfluent-platformksqldb

KSQL stream returning incorrect epoch conversion

I'm having a bit of a problem regarding Kafka KSQLs TIMESTAMPTOSTRING function. I am passing through two epochs, one for todays date and one for a different date. I am using JSON format and my streams are as follow:

CREATE STREAM TestStream
(
    Id BIGINT,
    AnotherDate BIGINT,
    TodaysDate BIGINT
) WITH(KAFKA_TOPIC='_account', VALUE_FORMAT='JSON');

When querying the TestStream, I am presented with the following data:

Test Stream enter image description here

I used the following site to check if the epochs are valid: https://www.epochconverter.com

However, when I run the following query:

SELECT 
  TIMESTAMPTOSTRING(TODAYSDATE, 'yyyy-MM-dd HH:mm:ss.SSS') AS TODAY, 
  TIMESTAMPTOSTRING(ANOTHERDATE, 'yyyy-MM-dd HH:mm:ss.SSS') AS ANOTHERDATE
FROM 
  TestStream EMIT CHANGES LIMIT 5;

I am presented with the following dates, which seems to be incorrect: Stream Query

enter image description here

Any assistance would be highly appreciated.

Solution

  • If you read the TIMESTAMPTOSTRING function documentation:

    Converts a BIGINT millisecond timestamp value into the string representation of the timestamp in the given format.

    Your epoch timestamps are at the second level and not at the millisecond level. That's why you have such results.

    If you just perform a simple arithmetic operation (epoch_in_secs * 1000) to convert the epochs held in your TODAYSDATE and ANOTHERDATE to milliseconds, it will work just fine.

    SELECT 
  TIMESTAMPTOSTRING(TODAYSDATE*1000, 'yyyy-MM-dd HH:mm:ss.SSS') AS TODAY, 
  TIMESTAMPTOSTRING(ANOTHERDATE*1000, 'yyyy-MM-dd HH:mm:ss.SSS') AS ANOTHERDATE
FROM 
  TestStream EMIT CHANGES LIMIT 5;

    I have performed a simple test on the confluent pageview tutorial and it works:

    SELECT 
  VIEWTIME_SEC,
  VIEWTIME,
  TIMESTAMPTOSTRING(VIEWTIME_SEC*1000, 'yyyy-MM-dd HH:mm:ss.SSS') AS TS_FROM_SEC,
  TIMESTAMPTOSTRING(VIEWTIME, 'yyyy-MM-dd HH:mm:ss.SSS') AS TS_FROM_MS
FROM 
  PAGEVIEWS_MARTELADA 
EMIT CHANGES;

    Results:

    +-------------+--------------+------------------------+------------------------+
|VIEWTIME_SEC |VIEWTIME      |TS_FROM_SEC             |TS_FROM_MS              |
+-------------+--------------+------------------------+------------------------+
|1605889400   |1605889400577 |2020-11-20 16:23:20.000 |2020-11-20 16:23:20.577 |
|1605889400   |1605889400902 |2020-11-20 16:23:20.000 |2020-11-20 16:23:20.902 |
|1605889401   |1605889401619 |2020-11-20 16:23:21.000 |2020-11-20 16:23:21.619 |
|1605889402   |1605889402556 |2020-11-20 16:23:22.000 |2020-11-20 16:23:22.556 |
|1605889403   |1605889403115 |2020-11-20 16:23:23.000 |2020-11-20 16:23:23.115 |
|1605889404   |1605889404080 |2020-11-20 16:23:24.000 |2020-11-20 16:23:24.080 |
|1605889404   |1605889404317 |2020-11-20 16:23:24.000 |2020-11-20 16:23:24.317 |
|1605889404   |1605889404372 |2020-11-20 16:23:24.000 |2020-11-20 16:23:24.372 |