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();

Answer 2

Two suggestions:

1. Since the string you receive contains a UTC indicator (Z), save the time in UTC to your database. If you’re allowed to, change the datatype in the PostgreSQL database to timestamp with time zone for this purpose.
2. Use java.time, the modern Java date and time API. Don’t save the date and time neither as a Timestamp nor as a String to the database. The java.sql.Timestamp class is poorly designed, a true hack on top of the already poorly designed java.util.Date class, and is long outdated.

The string you got from JSON is in ISO 8601 format. This is great because it’s an international standard and because the classes of java.time generally parse ISO 8601 format natively, that is, without any explicit formatter. To parse it:

    String fromJson = "2020-12-16T15:05:26.507Z";
    OffsetDateTime dateTime = OffsetDateTime.parse(fromJson);
    System.out.println(dateTime);

Output so far:

2020-12-16T15:05:26.507Z

OffsetDateTime also prints ISO 8601 format back. To insert into Postgress:

    String sql = "insert into your_table(your_timestamp_with_time_zone_col)"
            + " values(?);";
    PreparedStatement ps = yourDatabaseConnection.prepareStatement(sql);
    ps.setObject(1, dateTime);
    int rowsInserted = ps.executeUpdate();

If you cannot change the datatype in the database, you need to insert a LocalDateTime into your timestamp (without time zone) column instead:

    LocalDateTime dateTime = OffsetDateTime.parse(fromJson)
            .atZoneSameInstant(ZoneOffset.UTC)
            .toLocalDateTime();
    
    String sql = "insert into your_table(your_timestamp_col) values(?);";

The rest is the same as before.

I opted to convert to UTC in case. In your example the string was already in UTC, so the conversion was a no-operation; but it may not always be, so it’s better to do this.

Link

Oracle tutorial: Date Time explaining how to use java.time.

Answer 3

I think you can use another API of Timestamp

static Timestamp valueOf(LocalDateTime dateTime)

and change your code to:

    LocalDateTime localDateTime = LocalDateTime.parse("2020-12-16T15:05:26.507");
    Timestamp.valueOf(localDateTime);

And if you want to use a different timezone other than your system timezone, you can also use: ZonedDateTime

Then use ZonedDateTime to get the localDateTime, and pass it to Timestamp.valueOf().