How to convert Int to Date

[Rex Fenley]

Hello,

I'm using the Table API and I have a column which is an integer day since epoch. According to the docs [1] both `int` and `java.lang.Integer` are acceptable for DATE. However, if I try to use the SQL API to write a DATE out to the Elasticsearch connector for the INT column I receive an exception. How then should I go about converting to DATE?

Exception:

Caused by: org.apache.flink.table.api.ValidationException: Field types of query result and registered TableSink default_catalog.default_database.sink_es_people do not match.

Query schema: [... column: INT, ...]

Sink schema: [... column: DATE, ...]

I know this column is the culprit because when I make it INT on both ends it works.

How do I go about making my INT a DATE?

Thanks!

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time

--

[r_khachatryan]

Hello,

Do both of the types you use have the same nullability?

For a primitive int, the documentation you referred to says: "Output only if type is not nullable".

On Tue, Nov 17, 2020 at 7:49 AM Rex Fenley <[hidden email]> wrote:

Hello,

I'm using the Table API and I have a column which is an integer day since epoch. According to the docs [1] both `int` and `java.lang.Integer` are acceptable for DATE. However, if I try to use the SQL API to write a DATE out to the Elasticsearch connector for the INT column I receive an exception. How then should I go about converting to DATE?

Exception:

Caused by: org.apache.flink.table.api.ValidationException: Field types of query result and registered TableSink default_catalog.default_database.sink_es_people do not match.

Query schema: [... column: INT, ...]

Sink schema: [... column: DATE, ...]

I know this column is the culprit because when I make it INT on both ends it works.

How do I go about making my INT a DATE?

Thanks!

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time

--

Rex Fenley  |  Software Engineer - Mobile and Backend

Remind.com |  BLOG  |  FOLLOW US  |  LIKE US

[Timo Walther]

Hi Rex,

the classes mentioned in the documentation such as `int` and
`java.lang.Integer` are only used when you leave the SQL world to a UDF
or to a Java implementation in a sink.

But as a SQL user you only need to pay attention to the logical data
type. Those must match entirely or be a supported implicit cast.

Regards,
Timo

On 17.11.20 09:51, Khachatryan Roman wrote:

> Hello,
>
> Do both of the types you use have the same nullability?
> For a primitive int, the documentation you referred to says: "Output
> only if type is not nullable".
>
> Regards,
> Roman
>
>
> On Tue, Nov 17, 2020 at 7:49 AM Rex Fenley <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Hello,
>
>     I'm using the Table API and I have a column which is an integer day
>     since epoch. According to the docs [1] both `int` and
>     `java.lang.Integer` are acceptable for DATE. However, if I try to
>     use the SQL API to write a DATE out to the Elasticsearch connector
>     for the INT column I receive an exception. How then should I go
>     about converting to DATE?
>
>     Exception:
>     Caused by: org.apache.flink.table.api.ValidationException: Field
>     types of query result and registered TableSink
>     default_catalog.default_database.sink_es_people do not match.
>     Query schema: [... column: INT, ...]
>     Sink schema: [... column: DATE, ...]
>
>     I know this column is the culprit because when I make it INT on both
>     ends it works.
>
>     How do I go about making my INT a DATE?
>
>     Thanks!
>
>     [1]
>     https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time
>     <https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time>
>
>     --
>
>     Rex Fenley|Software Engineer - Mobile and Backend
>
>
>     Remind.com <https://www.remind.com/>| BLOG
>     <http://blog.remind.com/> | FOLLOW US
>     <https://twitter.com/remindhq> | LIKE US
>     <https://www.facebook.com/remindhq>
>

[Rex Fenley]

Looks like using a cast from INT to DATE worked out just fine.

Thanks!

On Tue, Nov 17, 2020 at 6:39 AM Timo Walther <[hidden email]> wrote:

Hi Rex,

the classes mentioned in the documentation such as `int` and
`java.lang.Integer` are only used when you leave the SQL world to a UDF
or to a Java implementation in a sink.

But as a SQL user you only need to pay attention to the logical data
type. Those must match entirely or be a supported implicit cast.

Regards,
Timo

On 17.11.20 09:51, Khachatryan Roman wrote:
> Hello,
>
> Do both of the types you use have the same nullability?
> For a primitive int, the documentation you referred to says: "Output
> only if type is not nullable".
>
> Regards,
> Roman
>
>
> On Tue, Nov 17, 2020 at 7:49 AM Rex Fenley <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Hello,
>
>     I'm using the Table API and I have a column which is an integer day
>     since epoch. According to the docs [1] both `int` and
>     `java.lang.Integer` are acceptable for DATE. However, if I try to
>     use the SQL API to write a DATE out to the Elasticsearch connector
>     for the INT column I receive an exception. How then should I go
>     about converting to DATE?
>
>     Exception:
>     Caused by: org.apache.flink.table.api.ValidationException: Field
>     types of query result and registered TableSink
>     default_catalog.default_database.sink_es_people do not match.
>     Query schema: [... column: INT, ...]
>     Sink schema: [... column: DATE, ...]
>
>     I know this column is the culprit because when I make it INT on both
>     ends it works.
>
>     How do I go about making my INT a DATE?
>
>     Thanks!
>
>     [1]
>     https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time
>     <https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/types.html#date-and-time>
>
>     --
>
>     Rex Fenley|Software Engineer - Mobile and Backend
>
>
>     Remind.com <https://www.remind.com/>| BLOG
>     <http://blog.remind.com/> | FOLLOW US
>     <https://twitter.com/remindhq> | LIKE US
>     <https://www.facebook.com/remindhq>
>

--