0

I have tried multiple ways one of them using SimpleDateFromatter and now trying

import java.sql.Timestamp;
public static String getCorrectTimeFormat(Timestamp time) {
        return time.toInstant().toString();
    }

But the problem is which I realized during writing unit test is, the time gets modified.

  Timestamp timeStamp = Timestamp.valueOf("2020-07-22 12:26:51.599");
  String res = UserUtil.getCorrectTimeFormat(timeStamp);
  assertThat(res).isEqualTo("2020-07-22T12:26:51.599Z");

This never passes as the it auto converts to "2020-07-22T11:26:51.599Z"

Arnav Karforma
  • 112
  • 2
  • 12

2 Answers2

2
  1. It’s best to avoid jqva.sql.Timestamp completely. I’ll show you how.
  2. The result you got is correct, as I think you have already discovered.

Get java.time types from your database

Since JDBC 4.2 we can directly get java.time types from a ResultSet. If your database value is a timestamp with time zone (recommended), for example:

    OffsetDateTime odt = rs.getObject(
            "your_timestamp_with_time_zone_column", OffsetDateTime.class);
    String utcString = odt.withOffsetSameInstant(ZoneOffset.UTC).toString();

If your database value is a timestamp without time zone (not recommended), we can only get a LocalDateTime from it, which doesn’t define a point in time. To convert to Instant we need to rely on knowing which time zone the database uses. For example:

    ZoneId datebaseTimeZone = ZoneId.of("Europe/Paris");
    LocalDateTime ldt = rs.getObject(
            "your_timestamp_column", LocalDateTime.class);
    String utcString = ldt.atZone(datebaseTimeZone)
            .withZoneSameInstant(ZoneOffset.UTC)
            .toString();

If your database uses UTC, which counts as an offset, it’s better to use ZoneOffset than ZoneId:

    ZoneOffset datebaseOffset = ZoneOffset.UTC;
    LocalDateTime ldt = rs.getObject(
            "your_timestamp_column", LocalDateTime.class);
    String utcString = ldt.atOffset(datebaseOffset).toString();

Your observed result is correct

java.sql.Timestamp confusingly prints in the default time zone of the JVM, and Timestamp.valueOf() equally confusingly assumes that time zone. So assuming that your time zone is at offset +01:00 at this time of year (such as Great Britain, Ireland, Portugal, Morocco and Tunesia, for example), the conversion from 2020-07-22 12:26:51.599 to 2020-07-22T11:26:51.599Z is correct.

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • Your understanding of the problem I was facing is completely correct. I have updated my db type to "timestamptz", but what are your thoughts on how to retrieve them in hibernate entity class (when I know my timestamp is in UTC). – Arnav Karforma Jul 23 '20 at 18:03
  • Hibernate 5+ supports java.time types, but I am not fully familiar with the details yet. The optimistic attempt is to declare the entity field `Instant`, but otherwise `OffsetDateTime`. – Ole V.V. Jul 23 '20 at 18:10
1

You can use java 8 time ZonedDateTime class :

//1 - default pattern
String timeStamp = "2019-03-27T10:15:30";
ZonedDateTime localTimeObj = ZonedDateTime.parse(time);

//2 - specified pattern
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss a z");
String timeStamp1 = "2019-03-27 10:15:30 AM";
ZonedDateTime localTimeObj1 = ZonedDateTime.parse(timeStamp1, formatter);

//To get LocalDate from ZonedDateTime
LocalDate localDate = localTimeObj1.toLocalDate()

//To get timestamp from zoneddatetime with utc timezone
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneOffset.UTC);
Timestamp timestamp = Timestamp.from(ZonedDateTime.now(ZoneOffset.UTC).toInstant());
Ananthapadmanabhan
  • 5,706
  • 6
  • 22
  • 39