Get the Gregorian date from a java.sql.Timestamp

user_10005456 0
Viewed 726

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

java-8 spring-boot date sql java
3 Answers

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

user_57728820

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);
    }
}
user_108195730

You can make use of java.time, which enables you to parse the datetime without considering a zone or offset but provides the possibility of adding one afterwards without changing or adjusting the values parsed:

public static void main(String[] args) {
    // let's assume you have a timestamp with the following value
    Timestamp ts = Timestamp.valueOf("2020-05-27 18:37:39");
    // then you can parse it to a datetime that doesn't consider a zone or offset
    LocalDateTime ldt = LocalDateTime.parse(ts.toString(),
                            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S"));
    // print it once
    System.out.println(ldt);
    // then add an offset without changing / adjusting the time values
    OffsetDateTime odt = ldt.atOffset(ZoneOffset.of("+05:30"));
    // and print that
    System.out.println(odt);
    // or use a default format to exactly meet your requirements
    System.out.println(odt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSX")));
}

This outputs

2020-05-27T18:37:39
2020-05-27T18:37:39+05:30
2020-05-27T18:37:39.000+0530
user_17121350
Related