Ksql: TIMESTAMP_FORMAT does not respect '' escaping

[singpolyma-shopify]

CREATE STREAM foo (thing_id BIGINT, event_timestamp VARCHAR) WITH (VALUE_FORMAT='JSON', KAFKA_TOPIC='triggers', TIMESTAMP='event_timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');

The above appears to work. But trying to select from the streams results in the following:

Caused by: Invalid value java.lang.IllegalArgumentException: Unknown pattern letter: T for configuration Invalid date format: yyyy-MM-dd''T''HH:mm:ssX Unknown pattern letter: T

Version: 5.0.0-beta30

[apurvam]

Try to use double quotes, like so ‘yyyy-MM-dd’‘T’‘HH:mm:ssX’. See: https://docs.confluent.io/5.0.0-beta30/ksql/docs/syntax-reference.html#create-stream

[psycotica0-shopify]

Is that not the same as what he posted?

[apurvam]

Not sure of the formatting, but it looks like there are two single quotes '' in @singpolyma-shopify's snippet, but a single double quote " in mine.

[psycotica0-shopify]

Oh. The docs definitely have two ' (single quotes), and in your message above you have two ' (single quotes).
Either way, " (double-quote) doesn't appear to work either.
I get:

Unknown pattern letter: T for configuration Invalid date format: yyyy-MM-dd"T"HH:mm:ssX Unknown pattern letter: T

[rmoff]

I am hitting this error too, on 5.0.0.
Tried two single quotes '', double quote ", and double backtick ``

Caused by: Invalid value java.lang.IllegalArgumentException: Unknown pattern letter: T for configuration Invalid date format: yyyy-MM-dd''T''HH:mm:ssX Unknown pattern letter: T
Caused by: Invalid value java.lang.IllegalArgumentException: Unknown pattern letter: T for configuration Invalid date format: yyyy-MM-dd"T"HH:mm:ssX Unknown pattern letter: T
Caused by: Invalid value java.lang.IllegalArgumentException: Unknown pattern letter: T for configuration Invalid date format: yyyy-MM-dd``T``HH:mm:ssX Unknown pattern letter: T

[rmoff]

It would be nice to be able to use the pre-defined formatters? e.g. ISO_INSTANT. Currently thows an error:

Caused by: Invalid value java.lang.IllegalArgumentException: Unknown pattern letter: I for configuration Invalid date format: ISO_INSTANT Unknown pattern letter: I

[rmoff]

Seems CSAS handles the datetime parsing differently from simply SELECT, which works fine with double single quotes:

SELECT TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd''T''HH:mm:ssX')  FROM ENVIRONMENT_DATA;
2018-08-08T17:02:13Z

[foresightyj]

Hit the same problem here. Still no solutions?

[foresightyj]

@apurvam I tried two single quotes and one double quotes. Neither works. All my kafka topics have timestamps like 2018-08-15T14:49:22.4372506+08:00. It is my first day trying out KSQL and now I am completely hindered by this bug.

[apurvam]

I've marked this as a bug.

[foresightyj]

Appreciated the help. Meanwhile, I will use unix timestamp for all my kafka topics.

[uurl]

@apurvam @rmoff Fixed

[keithharper]

As a temporary work-around, you can do the following:

Given a timestamp such as 2017-04-05T19:24:45Z:

1. Create a stream that declares the timestamp column as varchar, but does not set a TIMESTAMP in the WITH clause.

CREATE STREAM foo (thing_id BIGINT, event_timestamp VARCHAR) WITH (VALUE_FORMAT='JSON', KAFKA_TOPIC='triggers');

1. Create a stream using the previous stream as the base, but select the timestamp column using the stringtotimestamp function:

CREATE STREAM foo_with_time WITH(VALUE_FORMAT='JSON',TIMESTAMP='event_timestamp') AS \
select thing_id, stringtotimestamp(event_timestamp, 'yyyy-MM-dd''T''HH:mm:ss''Z''') as event_timestamp \
from foo;