CODEWITHSUNDEEP
javasql-server
user2824374
user2824374

Reputation: 220

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

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.

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

Upvotes: 1

Views: 124

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 339472

Data type

The Tag on your Question indicates you are using Microsoft SQL Server as your database engine.

According to the doc, you should be defining your column as the data type datetimeoffset. That type is akin to the SQL standard type TIMESTAMP WITH TIME ZONE which stores a moment.

Do not use the types smalldatetime, datetime, nor datetime2. These lack the context of a time zone or offset-from-UTC. So these cannot represent a moment, a specific point on the time line.

👉 Given your code snippets using the name uploadDateTime, your business logic requires tracking a moment. So your table column must be of type datetimeoffset.

Your first sentence says:

I'm trying to retrieve the datetime value

If you mean literally, datetime type in Microsoft SQL Server, you are using the wrong type for your column. This will cause you confusion and trouble.

OffsetDateTime

While you neglected to mention what framework library you are using with your @Column annotation. So I cannot say with certainty, but I can guess that Instant is the wrong Java class to use here.

The JDBC specification for JDBC 4.2 and later requires JDBC drivers to support the java.time classes. The spec maps values of the SQL standard type TIMESTAMP WITH TIME ZONE to the Java class java.time.OffsetDateTime. The Java class Instant is not mapped at all in JDBC.

After retrieving an OffsetDateTime, you can extract an Instant if needed: Instant instant = myOffsetDateTime.toInstant() ;.

Upvotes: -1

user21063779
user21063779

Reputation:

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

Upvotes: 3

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79877

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

Upvotes: -1

Related Questions