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.

CodePudding user response：

java.time

The following table depicts the mapping of ANSI SQL types with java.time types:

Parse the given Date-Time string into OffsetDateTime as shown below:

import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSSSSS XXX", Locale.ENGLISH);
        String strDateTime = "2021-10-28 02:36:08.000000  02:00";
        OffsetDateTime odt = OffsetDateTime.parse(strDateTime, dtf);
        System.out.println(odt);
    }
}

Output:

2021-10-28T02:36:08 02:00

ONLINE DEMO

Now, you can store this OffsetDateTime into the database as shown below:

PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();

Learn more about the modern Date-Time API^* from Trail: Date Time.

^{* If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8 APIs available through desugaring. Note that Android 8.0 Oreo already provides support for java.time.}

CodePudding user response：

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.

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.