Snowflake Error for timestamps

[Anonymous]

Hi, 

I am trying to import some data from mySQL to Snowflake. 

My schema from looks like this - the table I created in Snowflake:

CREATE TABLE status_history (
id int primary key,
consignment_id int,
from_status varchar(100),
to_status varchar(100),
created_at timestamp_ntz,
created_by varchar(255),
event varchar(255)
);

The initial schema of mySQL is this one:

I tried many different Date Patterns, but I was always getting the same error:

Exception in component tJDBCOutput_1 (consignment_status_history_full_load)

net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got TIMESTAMP_LTZ(9) for column CREATED_AT

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:88)

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:384)

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:421)

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:240)

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:180)

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:152)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:637)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeUpdateInternal(SnowflakeStatementV1.java:135)

at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeBatch(SnowflakePreparedStatementV1.java:1045)

[statistics] disconnected

at srp.consignment_status_history_full_load_0_1.consignment_status_history_full_load.tMysqlInput_1Process(consignment_status_history_full_load.java:1426)

at srp.consignment_status_history_full_load_0_1.consignment_status_history_full_load.tJDBCRow_1Process(consignment_status_history_full_load.java:694)

at srp.consignment_status_history_full_load_0_1.consignment_status_history_full_load.runJobInTOS(consignment_status_history_full_load.java:2015)

at srp.consignment_status_history_full_load_0_1.consignment_status_history_full_load.main(consignment_status_history_full_load.java:1609)

How should I format the timestamps to make it work?

[lojdr]

Hello,

https://docs.snowflake.net/manuals/user-guide/date-time-examples.html

I think you are using Snowflake definition of datetime (HH24:MIS), but you should use Java notation in the component (HH:mm:ss) and add there a timezone shift (e.g. -0500 which is "-Z" in Java notation).

Please see the link above and configuration of your database.

Regards

Lojdr