How to store ZonedDateTime of java in TIMESTAMPTZ in oracle?

I want to store the ZonedDateTime in TIMESTAMP WITH TIME ZONE datatype in oracle. If I am trying to store string directly as a String it throws not a valid month. then I found that I can convert it to the TIMESTAMPTZ in java then store because we need to convert the string to TIMESTAMPTZ and its throwing error.

String d = "2021-10-28 02:36:08.000000 +02:00";
TIMESTAMPTZ t = new TIMESTAMPTZ(con, d);
PreparedStatement ps = con.prepareStatement(query);
ps.setObject(1,t);

Error/stack trace:

Exception in thread "main" java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
  at java.sql.Timestamp.valueOf(Timestamp.java:251)
  at oracle.sql.TIMESTAMPTZ.toBytes(TIMESTAMPTZ.java:1919)
  at oracle.sql.TIMESTAMPTZ.<init>(TIMESTAMPTZ.java:253)
  at OracleSelectQuery.main(OracleSelectQuery.java:21)

Someone please look into this.

Answer

The three-arg TIMESTAMPTZ(Connection, Timestamp, ZoneId) constructor

According to the documentation of the Oracle TIMESTAMPTZ class (link at the bottom) it has a constructor that in addition to the connection takes a java.sql.Timestamp and a java.time.ZoneId as arguments (a funny mixture of an outdated and a modern Java class). Since we can extract a ZoneOffset from your string and ZoneOffset is a subclass of ZoneId, we can use this constructor for your purpose:

    String d = "2021-10-28 02:36:08.000000 +02:00";
    OffsetDateTime odt = OffsetDateTime.parse(d, PARSER);
    Instant inst = odt.toInstant();
    ZoneId offsetAsZoneId = odt.getOffset(); 
    TIMESTAMPTZ t = new TIMESTAMPTZ(con, Timestamp.from(inst), offsetAsZoneId);

I used this formatter for parsing:

private static final DateTimeFormatter PARSER = new DateTimeFormatterBuilder()
        .append(DateTimeFormatter.ISO_LOCAL_DATE)
        .appendLiteral(' ')
        .append(DateTimeFormatter.ISO_LOCAL_TIME)
        .appendLiteral(' ')
        .appendOffsetId()
        .toFormatter(Locale.ROOT);

You may also save a time zone to Oracle

That the constructor I used accepts a ZoneId opens the additional possibility that we may store a real time zone ID like Europe/Paris or Asia/Kolkata to the database rather than just a naked UTC offset. At least the way I read the Oracle database documentation, its timestamp with time zone data type can hold a time zone ID. The example given in the documentation is America/Los_Angeles.

For a simple example of converting a ZonedDateTime to a TIMESTAMPTZ:

    ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Asia/Kolkata"));
    Instant inst = zdt.toInstant();
    ZoneId zid = zdt.getZone(); 
    TIMESTAMPTZ t = new TIMESTAMPTZ(con , Timestamp.from(inst), zid);

Links

• Documentation of TIMESTAMPTZ and its TIMESTAMPTZ(Connection, Timestamp, ZoneId) constructor.
• Oracle database Datetime Data Types and Time Zone Support help, section Datetime and Interval Data Types. Scroll down to TIMESTAMP WITH TIME ZONE Data Type.