Get the Gregorian date from a java.sql.Timestamp

Question

i have executed a query with entity manager. it’s returning data from multiple tables. in that case i am getting list of object arrays. so i have written a loop as below.

for (Object[] row : rows) {
    row[0]; //row[0] has the date from database as timestamp.
}

here row[0] is a sql Timestamp.
if we evaluate with debugging, i am getting Timestamp with result: 2020-05-27 18:37:39.0.

i can see cdate, a private variable which has value as 2020-05-27T18:37:39.000+0530. it’s the Gregorian date. i can't use it because it’s a private variable and it is of type BaseCalendar.Date.

if you do something and convert it in to UTC, i am getting as 2020-05-27T13:07:39Z

BUT i want it as 2020-05-27T18:37:39.000+0530

Answer 1

I too recommend that you use java.time, the modern Java date and time API. If you can, modify your query not to return an old-fashioned Timestamp object but rather an Instant or at least a LocalDateTime.

Then you may do for example:

    // Example row
    Object[] row = { Instant.parse("2020-05-27T13:07:39.000Z") };
    System.out.println(row[0]);

    ZonedDateTime zdt = ((Instant) row[0]).atZone(ZoneId.systemDefault());
    System.out.println(zdt);

Output from this example is, when running in Asia/Kolkata time zone:

2020-05-27T13:07:39Z
2020-05-27T18:37:39+05:30[Asia/Kolkata]

If you can’t change the return type from your query, the correct conversion is:

    // Example row
    Object[] row = { Timestamp.from(Instant.parse("2020-05-27T13:07:39.000Z")) };
    System.out.println(row[0]);

    ZonedDateTime zdt = ((Timestamp) row[0]).toInstant()
            .atZone(ZoneId.systemDefault());
    System.out.println(zdt);

Output is:

2020-05-27 18:37:39.0
2020-05-27T18:37:39+05:30[Asia/Kolkata]

Please note: Don’t use any strings for the conversion, a more direct conversion exists.

I have assumed that you were after that value, not necessarily after the same format that you saw in your debugger. In case you did want that format: What you saw was the result of BaseCalendar.Date.toString(), and it’s a variant of ISO 8601 format, the international standard. To obtain it, use a formatter. For example:

    DateTimeFormatter formatter = new DateTimeFormatterBuilder()
            .append(DateTimeFormatter.ISO_LOCAL_DATE)
            .appendLiteral('T')
            .appendPattern("HH:mm:ss.SSSxx")
            .toFormatter();
    String formattedDateTime = zdt.format(formatter);
    System.out.println(formattedDateTime);

2020-05-27T18:37:39.000+0530

Links

• Oracle tutorial: Date Time explaining how to use java.time.
• Wikipedia article: ISO 8601

Answer 2

Use the modern date/time API as follows:

import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String args[]) {
        // Define the format
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSZ");

        // Parse the date/time string using the defined format
        OffsetDateTime odt = OffsetDateTime.parse("2020-05-27T18:37:39.000+0530", formatter);

        // Display
        System.out.println(odt);
    }
}