Of course! This is a very common and important topic in Java development, especially when working with databases. Let's break down how to handle SQL DATETIME (and related types) in Java.

The key is to understand the mapping between SQL types and Java types. The standard for this is the JDBC (Java Database Connectivity) API.

The Core Mapping: SQL to Java Types

First, you need to know which Java class corresponds to the SQL DATETIME type you're using. Different database systems use slightly different names and precisions.

Recommendation: While java.sql.* types are the "classic" JDBC way, the modern and highly recommended approach is to use the java.time package (available since Java 8) for all application logic. You should only convert to java.sql.* types right at the database boundary (when setting/getting from a PreparedStatement or ResultSet).

The "Classic" JDBC Approach (java.sql.*)

This is the traditional way to interact with databases. You'll find lots of legacy code using it.

A. Getting a DATETIME from the Database (ResultSet)

When you query a database column of type DATETIME, you retrieve it using getTimestamp().

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp; // The corresponding Java type
public class JdbcDatetimeExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        String sql = "SELECT created_at FROM orders WHERE id = 101";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                // Get the DATETIME as a java.sql.Timestamp
                Timestamp timestamp = rs.getTimestamp("created_at");
                if (timestamp != null) {
                    System.out.println("Retrieved Timestamp: " + timestamp);
                    System.out.println("To Date: " + timestamp.toDate()); // For legacy Date objects
                    System.out.println("To String: " + timestamp.toString());
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

B. Setting a DATETIME in the Database (PreparedStatement)

To insert or update a DATETIME column, you use setTimestamp().

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime; // For creating the value
public class JdbcSetDatetimeExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        // 1. Get the current date and time using the modern java.time API
        LocalDateTime now = LocalDateTime.now();
        // 2. Convert it to a java.sql.Timestamp for JDBC
        Timestamp timestampToInsert = Timestamp.valueOf(now);
        String sql = "INSERT INTO orders (order_name, created_at) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "My New Order");
            pstmt.setTimestamp(2, timestampToInsert); // Set the Timestamp
            int affectedRows = pstmt.executeUpdate();
            System.out.println(affectedRows + " row(s) inserted.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The Modern java.time Approach (Recommended)

Since Java 8, the java.time package provides a much better, immutable, and thread-safe API for date and time. You should use this for all your business logic. The challenge is bridging the gap with JDBC.

A. Mapping java.time to JDBC Types

JDBC 4.2 and later (Java 8+) added direct support for java.time types, which is the best way to handle this.

Example: Getting a DATETIME as LocalDateTime

import java.sql.*;
import java.time.LocalDateTime;
public class ModernJdbcDatetimeExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        String sql = "SELECT created_at FROM orders WHERE id = 101";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                // Directly get the DATETIME as a LocalDateTime
                // This requires a JDBC driver that supports JDBC 4.2 (most modern ones do)
                LocalDateTime localDateTime = rs.getObject("created_at", LocalDateTime.class);
                if (localDateTime != null) {
                    System.out.println("Retrieved LocalDateTime: " + localDateTime);
                    System.out.println("Formatted: " + localDateTime.format(java.time.format.DateTimeFormatter.ISO_LOCAL_DATE_TIME));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example: Setting a DATETIME from LocalDateTime

import java.sql.*;
import java.time.LocalDateTime;
public class ModernJdbcSetDatetimeExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        LocalDateTime now = LocalDateTime.now();
        String sql = "INSERT INTO orders (order_name, created_at) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "My Modern Order");
            // Directly set the LocalDateTime
            pstmt.setObject(2, now); // setObject is even more flexible
            // Or, you can be explicit:
            // pstmt.setTimestamp(2, Timestamp.valueOf(now));
            int affectedRows = pstmt.executeUpdate();
            System.out.println(affectedRows + " row(s) inserted.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Bridging the Gap: Legacy Code with java.time

What if you're stuck with an older JDBC driver (pre-4.2) or a legacy codebase that uses java.util.Date? You'll have to do manual conversions.

java.sql.Timestamp ↔ java.time.LocalDateTime

This is the most common conversion.

import java.sql.Timestamp;
import java.time.LocalDateTime;
public class TimeConversion {
    // Convert LocalDateTime to java.sql.Timestamp
    public static Timestamp toTimestamp(LocalDateTime localDateTime) {
        return Timestamp.valueOf(localDateTime);
    }
    // Convert java.sql.Timestamp to LocalDateTime
    public static LocalDateTime toLocalDateTime(Timestamp timestamp) {
        // A timestamp can be null, so handle that
        return timestamp == null ? null : timestamp.toLocalDateTime();
    }
    public static void main(String[] args) {
        LocalDateTime now = LocalDateTime.now();
        System.out.println("Original LocalDateTime: " + now);
        Timestamp ts = toTimestamp(now);
        System.out.println("Converted to Timestamp: " + ts);
        LocalDateTime ldt = toLocalDateTime(ts);
        System.out.println("Converted back to LocalDateTime: " + ldt);
    }
}

java.sql.Timestamp ↔ java.util.Date

This is for integrating with very old code. java.sql.Timestamp is actually a subclass of java.util.Date.

import java.sql.Timestamp;
import java.util.Date;
public class LegacyDateConversion {
    public static void main(String[] args) {
        Timestamp ts = new Timestamp(System.currentTimeMillis());
        // Timestamp -> Date (Upcasting)
        Date date = ts;
        System.out.println("As a Date: " + date);
        System.out.println("Note: nanos are lost if you just use Date methods.");
        // Date -> Timestamp (Downcasting, be careful!)
        if (date instanceof Timestamp) {
            Timestamp originalTs = (Timestamp) date;
            System.out.println("Cast back to Timestamp: " + originalTs);
        }
    }
}

Summary & Best Practices

1. For Application Logic: Always use java.time (LocalDate, LocalDateTime, etc.). It's safer, clearer, and less error-prone than the legacy date APIs.
2. For Database Interaction:
   • Best: Use PreparedStatement.setObject() and ResultSet.getObject() with java.time types. This is clean and requires JDBC 4.2+.
   • Good: Use PreparedStatement.setTimestamp() and ResultSet.getTimestamp(), converting your java.time.LocalDateTime to/from java.sql.Timestamp using Timestamp.valueOf() and Timestamp.toLocalDateTime(). This works with all JDBC versions.
3. Avoid java.util.Date and java.sql.Date in new code. They are legacy, mutable, and have design flaws.
4. Be Aware of Timezones: DATETIME in databases like MySQL is typically "wall time" without timezone information. java.time.LocalDateTime is a perfect match for this. If your database column stores timezone-aware data (like TIMESTAMP WITH TIME ZONE), use java.time.OffsetDateTime or ZonedDateTime instead.