Timestamp field appears not to work in DESCRIBE EXTENDED

[rmoff]

Source records have a timestamp column, ORDER_DATE:

ksql> CREATE STREAM ORDERS_SRC WITH (KAFKA_TOPIC='ora-ogg-SOE2-ORDERS-avro', VALUE_FORMAT='AVRO');

 Message
----------------
 Stream created
----------------
ksql>
ksql> SELECT TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss') , ORDER_ID, ORDER_DATE FROM ORDERS_SRC LIMIT 5;
2018-01-08 14:17:01 | 71490 | 2009-01-05 23:00:00.000000000

Covert the ORDER_DATE string to an epoch timestamp, and also retain the original ROWTIME for reference (as EXTRACT_TS):

ksql> CREATE STREAM ORDERS_INT_01 AS SELECT ROWTIME AS EXTRACT_TS, ORDER_DATE, STRINGTOTIMESTAMP(ORDER_DATE,'yyyy-MM-dd HH:mm:ss.SSSSSSSSS') AS ORDER_DATE_EPOCH, ORDER_ID, ORDER_STATUS, ORDER_TOTAL FROM ORDERS_SRC;

 Message
----------------------------
 Stream created and running
----------------------------
ksql> SELECT ORDER_ID, ORDER_TOTAL, TIMESTAMPTOSTRING(EXTRACT_TS,'yyyy-MM-dd HH:mm:ss'), TIMESTAMPTOSTRING(ORDER_DATE_EPOCH, 'yyyy-MM-dd HH:mm:ss') FROM ORDERS_INT_01 LIMIT 5;
71490 | 5618.0 | 2018-01-08 14:17:01 | 2009-01-05 23:00:00

Create a derived stream that uses ORDER_DATE as its timestamp:

ksql> CREATE STREAM ORDERS AS SELECT EXTRACT_TS, ORDER_DATE_EPOCH, ORDER_ID, ORDER_DATE, ORDER_STATUS, ORDER_TOTAL FROM ORDERS_INT_01 WITH (TIMESTAMP ='ORDER_DATE_EPOCH');

 Message
----------------------------
 Stream created and running
----------------------------
ksql> SELECT TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss') , ORDER_DATE, ORDER_ID, ORDER_TOTAL FROM ORDERS LIMIT 5;
2009-01-05 23:00:00 | 2009-01-05 23:00:00.000000000 | 71490 | 5618.0

However, the Timestamp field still shows as Not set

ksql> DESCRIBE EXTENDED ORDERS;

Type                 : STREAM
Key field            :
Timestamp field      : Not set - using <ROWTIME>
Key format           : STRING
Value format         : AVRO
Kafka output topic   : ORDERS (partitions: 4, replication: 1)

 Field            | Type
----------------------------------------------
 ROWTIME          | BIGINT           (system)
 ROWKEY           | VARCHAR(STRING)  (system)
 EXTRACT_TS       | BIGINT
 ORDER_DATE_EPOCH | BIGINT
 ORDER_ID         | BIGINT
 ORDER_DATE       | VARCHAR(STRING)
 ORDER_STATUS     | BIGINT
 ORDER_TOTAL      | DOUBLE
----------------------------------------------

Queries that write into this STREAM
-----------------------------------
id:CSAS_ORDERS - CREATE STREAM ORDERS AS SELECT EXTRACT_TS, ORDER_DATE_EPOCH, ORDER_ID, ORDER_DATE, ORDER_STATUS, ORDER_TOTAL FROM ORDERS_INT_01 WITH (TIMESTAMP ='ORDER_DATE_EPOCH');

For query topology and execution plan please run: EXPLAIN <QueryId>

Local runtime statistics
------------------------
messages-per-sec:   2325.85   total-messages:    231569     last-message: 1/8/18 2:44:19 PM GMT
 failed-messages:         0 failed-messages-per-sec:         0      last-failed:       n/a
(Statistics of the local KSQL server interaction with the Kafka topic ORDERS)

Even if a different field is used, the Timestamp field is still shown as Not set:

ksql> CREATE STREAM ORDERS2 AS SELECT EXTRACT_TS, ORDER_DATE_EPOCH, ORDER_ID, ORDER_DATE, ORDER_STATUS, ORDER_TOTAL FROM ORDERS_INT_01 WITH (TIMESTAMP ='EXTRACT_TS');

 Message
----------------------------
 Stream created and running
----------------------------
ksql> DESCRIBE EXTENDED ORDERS2;

Type                 : STREAM
Key field            :
Timestamp field      : Not set - using <ROWTIME>
Key format           : STRING
Value format         : AVRO
Kafka output topic   : ORDERS2 (partitions: 4, replication: 1)

 Field            | Type
----------------------------------------------
 ROWTIME          | BIGINT           (system)
 ROWKEY           | VARCHAR(STRING)  (system)
 EXTRACT_TS       | BIGINT
 ORDER_DATE_EPOCH | BIGINT
 ORDER_ID         | BIGINT
 ORDER_DATE       | VARCHAR(STRING)
 ORDER_STATUS     | BIGINT
 ORDER_TOTAL      | DOUBLE
----------------------------------------------

Queries that write into this STREAM
-----------------------------------
id:CSAS_ORDERS2 - CREATE STREAM ORDERS2 AS SELECT EXTRACT_TS, ORDER_DATE_EPOCH, ORDER_ID, ORDER_DATE, ORDER_STATUS, ORDER_TOTAL FROM ORDERS_INT_01 WITH (TIMESTAMP ='EXTRACT_TS');

For query topology and execution plan please run: EXPLAIN <QueryId>

Local runtime statistics
------------------------
messages-per-sec:   2773.27   total-messages:    275457     last-message: 1/8/18 2:45:48 PM GMT
 failed-messages:         0 failed-messages-per-sec:         0      last-failed:       n/a
(Statistics of the local KSQL server interaction with the Kafka topic ORDERS2)
ksql> SELECT TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss') , ORDER_DATE, ORDER_ID, ORDER_TOTAL FROM ORDERS2 LIMIT 5;
2018-01-08 14:17:01 | 2009-01-05 23:00:00.000000000 | 71490 | 5618.0

[rmoff]

Per #604 I was using the WITH clause in the wrong location. Having fixed this, the Timestamp field shows correctly in DESCRIBE EXTENDED. Closing.