How to retrieve the date from the database in a proper way using Java?-CodePudding

I'm trying to retrieve the datetime value from my database and I have declared the upload_date_time to be Instant and this is how the value is stored in database.

However, when I attempted to retrieve the value from the database it is giving me '2022-03-01T14:00:00Z'.

UploadOrder Table

@NotNull
@Column(name = "upload_date_time", nullable = false)
private Instant uploadDateTime;

public Instant getUploadDateTime() {
    return uploadDateTime;
}

public UploadOrder uploadDateTime(Instant uploadDateTime) {
    this.uploadDateTime= uploadDateTime;
    return this;
}

public void setUploadDateTime(Instant uploadDateTime) {
    this.uploadDateTime= uploadDateTime;
}

Service

public PrePickOrderOverPickEmailDTO generateOverPickEmails() {
log.debug("Request to retrieve Upload data : {}");
List<UploadOrder> uploadOrders = uploadPickRepository.findAllByEmailSent(false);
String data = "";
for (UploadOrder order : uploadOrders) {
        data = data  
        "Upload Date: "   order.getUploadDateTime()  "\n"  
         // do others
 }
}

Curious to know what am I doing wrong here. Any help would be great. Thanks

CodePudding user response：

Since you commented your database timezone is UTC 10:00, then what you are seeing is correct.

2022-03-02T00:00 10:00 is the same date as 2022-03-01T14:00:00Z, Z is UTC( 0), so they both represent the same date value, just represented with different time zones.

You can verify this by:

jshell> java.time.OffsetDateTime.parse("2022-03-02T00:00 10:00").toInstant()
$4 ==> 2022-03-01T14:00:00Z

To localize the UTC instant to a different zone, you can use Instant::atZone() or Instant::atOffset().

CodePudding user response：

An Instant is a moment in time, without any time zone being specified. And the Instant that you're actually storing is 2pm on 1 March UTC, which is the same as midnight on 2 March AEST.

When you're looking at this value through your SQL client, the SQL client is translating it to your local time, so you're seeing midnight on 2 March.

When you're retrieving it with your Java program, you're displaying it without specifying a time zone or time offset. When you do that to an Instant, you get to see it in UTC.

What you actually want to do is convert the Instant to a LocalDateTime before you display it - that way, you'll be able to see it in AEST, or whatever your local time zone happens to be. The LocalDateTime class has a static method called ofInstant, which will do that. You need to pass the ZoneId for your timezone, like this.

LocalDateTime timeInBrisbane = LocalDateTime.ofInstant(uploadDateTime, ZoneId.of("Australia/Brisbane"));

or even like this, to get the system default time zone.

LocalDateTime timeInDefaultZone = LocalDateTime.ofInstant(uploadDateTime, ZoneId.systemDefault());