Question

Cannot insert StreamSets ZONED_DATETIME into PostgreSQL timestamptz column

  • 8 March 2022
  • 3 replies
  • 88 views

Userlevel 1

I have run into a problem while working with timezones. It seems I cannot use the JDBC destination to dump ZONE_DATETIME fields into a PostgreSQL timestamptz column. The error received in StreamSets is a bit ambiguous, but the problem is clearly the ZONE_DATETIME typed fields as it works if I exchange them with DATETIME fields. Based on the name, I am assuming that ZONED_DATETIME are actually Java ZonedDateTime types. Some research into the PostgreSQL JDBC driver found that it does not support ZonedDateTime, but rather OffsetDateTime (Not sure why the jdbc driver cannot simply convert the ZonedDateTime to OffsetDateTime since they both describe points in time, but I digress). Do you have any suggestions for handling this situation?


3 replies

Userlevel 4
Badge

@mblahay you are right with your findings, ZonedDateTime isn't directly supported with postgres but rather support OffsetDateTime. 

ref : https://jdbc.postgresql.org/documentation/head/8-date-time.html 

However, Streamsets Data Collector 3.20.0+ already take care of this ( will convert ZonedDateTime to  OffsetDateTime automatically) Please verify you SDC version.

ref https://issues.streamsets.com/browse/SDC-15995 

 

Userlevel 1

 I’m using 3.22.1

Userlevel 4
Badge

@mblahay  Could you share your pipeline and sdc logs which have the error trace ( & relevant screenshot).

Reply