How to store ZonedDateTime of java in TIMESTAMPTZ in oracle?

Question

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.

After [your previous question](https://stackoverflow.com/questions/69451932/how-to-convert-a-2021-09-29t170431-0000-0530-to-2021-09-29-170431-0000000) I am tempted to ask whether you just want to save the UTC offset (+02:00) or you need to save a time zone (like for instance Europe/Paris)? — Ole V.V., Oct 07 '21 at 16:57

score 2 · Answer 1 · answered Oct 07 '21 at 12:11

java.time

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

ANSI SQL	Java SE 8
DATE	LocalDate
TIME	LocalTime
TIMESTAMP	LocalDateTime
TIME WITH TIMEZONE	OffsetTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

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.}

Thanks, Resolved the issue Though I have used bit different ways — Pravin Mangalam, Oct 08 '21 at 17:32

Ole V.V. · Accepted Answer · 2021-10-08T11:59:09.843

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.

Thanks, Resolved the issue, I used this way – Pravin Mangalam Oct 08 '21 at 17:32 — Pravin Mangalam, Oct 08 '21 at 17:32

How to store ZonedDateTime of java in TIMESTAMPTZ in oracle?

2 Answers2

java.time

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

You may also save a time zone to Oracle

Links