Converting date to timestamp with timezone in Java

Question

I have a PostgreSQL database with a column defined as timestamp

I am receiving the date with timezone format yyyy-MM-ddTHH:mm:ss.SSSX", for example 2020-12-16T15:05:26.507Z. If I want to insert this into a column with timestamp it will throw "Timestamp format must be yyyy-mm-dd hh:mm:ss"

I am doing

Timestamp.valueOf("2020-12-16T15:05:26.507")

Now the timezone date comes from a JSON, so I am taking it as a string string for now.

How do I go about converting this to simple Timestamp format? to 2020-12-16 15:05:26

Answer 1

The following table summarizes the PostgreSQL column type mapping with Java SE 8 date-time types:

--------------------------------------------------
PostgreSQL                          Java SE 8
==================================================
DATE                                LocalDate
--------------------------------------------------
TIME [ WITHOUT TIMEZONE ]           LocalTime
--------------------------------------------------
TIMESTAMP [ WITHOUT TIMEZONE ]      LocalDateTime
--------------------------------------------------
TIMESTAMP WITH TIMEZONE             OffsetDateTime
--------------------------------------------------

Note that ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported. Also, note that all OffsetDateTime instances will have to be in UTC (which has a time zone offset of +00:00 hours). This is because the backend stores them as UTC.

Thus, there are two options.

Option - 1 (Recommended):

Change the column type to TIMESTAMP WITH TIMEZONE. This is recommended because your date-time string has Z which stands for Zulu date-time or UTC date-time. Using OffsetDateTime, you can parse this date-time string without requiring any DateTimeFormatter explicitly.

Demo:

import java.time.OffsetDateTime;

public class Main {
    public static void main(String[] args) {
        OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
        System.out.println(odt);
    }
}

Output:

2020-12-16T15:05:26.507Z

Given below is an example of how to use this OffsetDateTime for DB CRUD operations:

OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();

Option - 2:

If you still want to keep the column type as TIMESTAMP [ WITHOUT TIMEZONE ], you can get the LocalDateTime from OffsetDateTime and use the same as shown below:

OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
LocalDateTime ldt = odt.toLocalDateTime();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, ldt);
st.executeUpdate();
st.close();