Skip to main content
Question

Cannot insert StreamSets ZONED_DATETIME into PostgreSQL timestamptz column

  • March 8, 2022
  • 3 replies
  • 144 views

mblahay
Discovered Fame

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

Rishi
StreamSets Employee
Forum|alt.badge.img
  • StreamSets Employee
  • 96 replies
  • March 11, 2022

@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 

 


mblahay
Discovered Fame
  • Author
  • Discovered Fame
  • 13 replies
  • March 11, 2022

 I’m using 3.22.1


Rishi
StreamSets Employee
Forum|alt.badge.img
  • StreamSets Employee
  • 96 replies
  • March 11, 2022

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