postgresql - How to convert ja.sql.timestamp to given format using java

Question

Storing the time in UTC timezone in DB.After fetching from the DB Need to convert the time in the given format "20201218064312+0800"

here + should take care of the summer time as well.

Answer 1

You should retrieve the date-time into a LocalDate object as shown below:

LocalDate ldt = rs.getObject(1, LocalDate.class));

Then convert ldt into an OffsetDateTime odt using LocalDateTime#atOffset. Finally, convert odt into ZonedDateTime with the same instant by using odt.atZoneSameInstant to which you will have to pass the ZoneId as the argument. ZonedDateTime has been designed to take care of variation in time due to summer/winter change automatically.

Finally, use the appropriate DateTimeFormatter to print the date-time in the desired format.

Demo:

import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) {
        // I have created this instance of LocalDateTime for the purpose of demo. This
        // should come from your ResultSet
        LocalDateTime ldt = LocalDateTime.parse("2020-12-15 17:38:40.962",
                DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS"));
        OffsetDateTime odt = ldt.atOffset(ZoneOffset.UTC);
        System.out.println(odt);

        // Convert the odt to Zone
        ZoneId zoneId = ZoneId.of("Europe/Vatican");
        ZonedDateTime zdt = odt.atZoneSameInstant(zoneId);
        System.out.println(zdt);

        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("uuuuMMddHHmmssXX");
        System.out.println(zdt.format(dtf));
    }
}

Output:

2020-12-15T17:38:40.962Z
2020-12-15T18:38:40.962+01:00[Europe/Vatican]
20201215183840+0100

A quick demo of how ZonedDateTime automatically adjusts the timezone offset:

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;

public class Main {
    public static void main(String[] args) {
        LocalDateTime ldtSummer = LocalDateTime.of(2017, 06, 02, 6, 0, 0);
        LocalDateTime ldtWinter = LocalDateTime.of(2017, 12, 02, 6, 0, 0);
        ZoneId nyZone = ZoneId.of("America/New_York");
        ZonedDateTime nyZdtSummer = ldtSummer.atZone(nyZone);
        ZonedDateTime nyZdtWinter = ldtWinter.atZone(nyZone);
        System.out.println(nyZdtSummer);
        System.out.println(nyZdtWinter);
    }
}

Output:

2017-06-02T06:00-04:00[America/New_York]
2017-12-02T06:00-05:00[America/New_York]