Dealing with TimeZone in Java8 and/with Postgresql

DateTimeConversionWithTz.java

import static java.time.temporal.ChronoField.HOUR_OF_DAY;
import static java.time.temporal.ChronoField.MINUTE_OF_HOUR;
import static java.time.temporal.ChronoField.SECOND_OF_MINUTE;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.TextStyle;
import java.util.TimeZone;

/**
 *
 */
public class DateTimeConversionWithTz {


  public static void main(String[] args) throws SQLException {
    new DateTest().test();
  }


  public void test() throws SQLException {

    // 2018-12-27 07:30:30 MST - we will convert this to GMT (equivalent of UTC), store it in db
    // and then convert it back to MST

    DateTimeFormatter formatter = new DateTimeFormatterBuilder()
        .append(DateTimeFormatter.ISO_LOCAL_DATE)
        .appendLiteral(' ').appendValue(HOUR_OF_DAY, 2)
        .appendLiteral(':')
        .appendValue(MINUTE_OF_HOUR, 2)
        .optionalStart()
        .appendLiteral(':')
        .appendValue(SECOND_OF_MINUTE, 2).optionalEnd().optionalStart()
        .appendLiteral(' ').appendZoneText(TextStyle.SHORT_STANDALONE).optionalEnd()
        .toFormatter();

    ZonedDateTime z1 = ZonedDateTime.parse("2018-12-27 07:30:30 MST", formatter);
    ZonedDateTime atGmt = z1.withZoneSameInstant(TimeZone.getTimeZone("GMT").toZoneId());
    System.out.println(z1.format(formatter) + " - " + atGmt.format(formatter));

    //insert into db
    System.out.println("inserting into db .. ");
    Connection connection = DriverManager
        .getConnection("jdbc:postgresql://localhost:5432/temp_db", "keycloak", "password");
    PreparedStatement st = connection.prepareStatement("insert into ts_test values(?,?)");
    //System.out.println(atGmt.toInstant() + " -- " + z1.toInstant());
    Timestamp ts = Timestamp.from(atGmt.toInstant());
    st.setInt(1, 9);
    st.setTimestamp(2, ts);

    int rs = st.executeUpdate();

    System.out.println("reading back from db ..");
    //retrieve from db..
    PreparedStatement st2 = connection.prepareStatement("select t1 from ts_test");
    ResultSet rs2 = st2.executeQuery();
    if (rs2.next()) {
      Timestamp res = rs2.getTimestamp("t1");
      System.out.println(ZonedDateTime.ofInstant(res.toInstant(), z1.getZone()).format(formatter));
    } else {
      System.err.println("No such row");
    }

    connection.close();


  }
}

Sample Result -

2018-12-27 07:30:30 MST - 2018-12-27 14:30:30 GMT
inserting into db ..
reading back from db ..
2018-12-27 07:30:30 MST

Process finished with exit code 0

FYI .. Postgresql stores timestamp as "timetstamp with timezone" (alias timezonetz) in GMT (UTC) . It stores everything to UTC/GMT and applications have to convert it back to the required TZ (unless the client connection has a timezone set as a session configuration)

Here's the DDL for the table used in the example

create table ts_test(id int not null primary key,t1 timestamptz not null);