KSQL stream returning incorrect epoch conversion

Question

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

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

Any assistance would be highly appreciated.

Alexandre Juma Alexandre Juma · Accepted Answer · 2020-11-20T13:13:00

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 |

KSQL stream returning incorrect epoch conversion

1 Answers