Get the Timestamp value from database as same as it is using java

Question

In a MySQL database table, there is column named loginTime and its type is timestamp. Its value is 2023-02-14 10:02:26.

when I get the value to date type variable using java and I get value as 2023-02-14 10:02. How can I get the date with seconds also. As same as the database. I tried but still unable to find solution.

Answer 1

tl;dr

Correct modern solution:

myResultSet.getObject( … , OffsetDateTime.class )  // Returns an `java.time.OffsetDateTime` object.

You said:

column named loginTime and its type is timestamp. Its value is 2023-02-14 10:02:26.

No, you are incorrect. The TIMESTAMP type represents a date with time-of-day as seen in UTC. Your reported value lacks the indication of an offset from UTC of zero, which is vital info.

Details

The accepted Answer is ill-advised in three ways:

• The first part uses terribly flawed legacy date-time classes that were years ago supplanted by the modern java.time classes defined in JSR 310.
• The second part employs the LocalDateTime class. LocalDateTime is the wrong class to use here. The TIMESTAMP data type in MySQL is a date with time as seen in UTC. The LocalDateTime lacks any concept of offset, so that solution tragically discards valuable information. Note earlier Comment by Ole V.V.
• Both parts focus on parsing text retrieved from the database. Instead, date-time column values should be retrieved into date-time types in Java. No need to resort to text.

First step is to read the documentation for the TIMESTAMP data type in MySQL 8.0. To quote:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Those mentions of “UTC” are vital. That term is an abbreviation meaning “with an offset from the temporal meridian of UTC of zero hours-minutes-seconds”.

In the SQL Standard, this type is equivalent to TIMESTAMP WITH TIME ZONE (where the Standard authors incorrectly used the term “time zone” where they meant “offset”).

java.time.OffsetDateTime

So the appropriate Java type mapped to such a column in the JDBC 4.2+ specification is OffsetDateTime.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

To write such a value to the database, pass your OffsetDateTime object to PreparedStatement#setObject.

Answer 2

You most probably need to include the timestamp class which allows the JDBC API to identify a java.util.Date object as an SQL TIMESTAMP value. Here we set a timestamp value to include fractional seconds and display this:

// old classes and approach in use here, read on for more 
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class TimestampExample {
    public static void main(String[] args) {
        try {
            String timestampStr = "2023-02-14 10:02:26.123";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
            Timestamp loginTime = new Timestamp(dateFormat.parse(timestampStr).getTime());
            System.out.println("loginTime: " + loginTime);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Added: Here is an alternative using Java.time (see comments under answer)

// don't use this approach if the source data has embedded time zone information 
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class TimestampExample {
    public static void main(String[] args) {
        try {
            String timestampStr = "2023-02-14 10:02:26.123";
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
            LocalDateTime loginTime = LocalDateTime.parse(timestampStr, formatter);
            System.out.println("loginTime: " + loginTime);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

There was never an intent to focus on handling date/time information as text, the snippets above were simply designed to easily visualize sub-second precision. Based on the rolling wave of advice from the comments I'll presume that something along the following lines will more correctly handle a MySQL "timestamp".

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.OffsetDateTime;

public class TimestampExample {
    public static void main(String[] args) {
        try {
            // Assume the connection has been established

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT login_time FROM users WHERE id = 1");

            if (rs.next()) {
                OffsetDateTime loginTime = rs.getObject("login_time", OffsetDateTime.class);
                System.out.println("loginTime: " + loginTime);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

this variant is untested