SQL Datatime Data Populated (JDBC Sink) Incorrectly

[Jered Gosse]

When running a JDBC source connector (timestamp mode) in conjunction with Sink connector the target datatime column data converted to GMT instead of retaining in local CST timezone.  Below are the SQL Server tables, values, connector properties and example data.

Source table create statement:

CREATE TABLE [dbo].[KAFKA_DATE_TEST2] ([DATA] [varchar](20) NULL, [DATE_1] [datetime] NULL, [DATE_2] [datetime] NULL)

Insert source test record:

INSERT INTO [dbo].[KAFKA_DATE_TEST2] SELECT 'VALUE1', '2016-11-30 13:00:00', '2016-11-30 13:00:00'

Validate source data:

Select * from [dbo].[KAFKA_DATE_TEST2]

DATA = 'VALUE1'

DATE_1 = 2016-11-30 13:00:00.000

DATE_2 = 2016-11-30 13:00:00.000

Below are the resulting values within the SQL Server table after running the JDBC Source and Sink connectors.

DATA = 'VALUE1'

DATE_1 = 2016-11-30 07:00:00.0000000

DATE_2 = 2016-11-30 07:00:00.0000000

I have included the JDBC source and sink connector properties below.  If I create the same table structure with a generated ID column and utilize incrementing mode, source and target table values match.  Is there a setting I am missing or is this a potential bug?

JDBC Source Connector Properties

name=jdbc-src-kafka-date-test-timestamp

connector.class=io.confluent.connect.jdbc.JdbcSourceConnector

tasks.max=1

 

table.types=TABLE

table.whitelist=KAFKA_DATE_TEST2

mode=timepstamp

timestamp.column.name=DATE_1

 

validate.non.null=false

topic.prefix=TGT-

JDBC Sink Connector Properties

name=jdbc-sink-date-test

connector.class=io.confluent.connect.jdbc.JdbcSinkConnector

tasks.max=1

topics=TGT- KAFKA_DATE_TEST2

auto.create=true

[Shikhar Bhushan]

Hi Jered,

It would be helpful to know what the data looks like in Kafka. Could you use the kafka-avro-console-consumer on the topic populated by the source to see if the timestamps are in UTC or the local TZ?

I think we should consistently use UTC for data in Kafka, and specify a UTC Calendar instance when getting times out of a ResultSet in the source, or binding times to a Statement in the sink. This is because the logical date/time types modelled in Connect are not TZ-aware (neither is SQL Server's `datetime` type). As far as I can tell all extraction on the source side uses UTC. Opened an issue on the sink side, though not sure that's responsible for what you are seeing: https://github.com/confluentinc/kafka-connect-jdbc/issues/174

Best,

Shikhar

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/2def8259-843c-41f2-bc39-a5360aee22b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shikhar Bhushan]

Given what you mentioned about seeing local times coming out on the destination table when using `incrementing` mode but UTC times when using `timestamp` mode, I suppose there is a bug in the source connector. I can't spot it though, please feel free to open a GH issue.

[Jered Gosse]

Hi Shikhar,

I was able to run the Kafka-console-consumer against the topic and below is the resulting message.  

{"schema":{"type":"struct","fields":[{"type":"string","optional":true,"field":"DATA"},{"type":"int64","optional":true,"name":"org.apache.kafka.connect.data.Timestamp","version":1,"field":"DATE_1"},{"type":"int64","optional":true,"name":"org.apache.kafka.connect.data.Timestamp","version":1,"field":"DATE_2"}],"optional":false,"name":"KAFKA_DATE_TEST2"},"payload":{"DATA":"VALUE1","DATE_1":1480510800000,"DATE_2":1480510800000}}

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.

[Shikhar Bhushan]

Hi Jered,

My understanding based on this is that the source connector is reading the data you manually inserted as UTC epoch timestamp 1480510800000 (30 Nov 2016 13:00:00 GMT) and producing it to the topic as such, but when the sink connector inserts it this bug is affecting you: https://github.com/confluentinc/kafka-connect-jdbc/issues/174

I am not sure what combination of server timezone and implicit/explicit session timezones are coming into play at the points it is not explicitly specified (i.e. when you manually insert, or when the sink inserts in the absence of the above fix).

Would you be able to try with a build of the current master branch of the connector?

Best,

Shikhar

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/2def8259-843c-41f2-bc39-a5360aee22b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/9298b4d4-a852-49a5-a228-c62b200551ea%40googlegroups.com.

[Jered Gosse]

Hi Shikhar,

We are working on setting up a set of machines to download and perform builds on the latest versions of code.  Unfortunately, I me take a little while but I will keep you posted when I am able to test the change.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/2def8259-843c-41f2-bc39-a5360aee22b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.