【SQL】DATETIME 和 TIMESTAMP 的区别

在 SQL 中，DATETIME 和 TIMESTAMP 是用于存储日期和时间数据的两种数据类型，但它们在存储范围、时区处理和使用场景上有显著区别。以下是详细的解释：

1. DATETIME

• 定义: DATETIME 类型用于存储日期和时间，精度为秒。
• 格式: YYYY-MM-DD HH:MM:SS
• 范围: 从 1000-01-01 00:00:00 到 9999-12-31 23:59:59
• 存储: 存储的是输入的日期和时间，不受时区影响。
• 用途: 适用于需要存储特定时间点而不考虑时区的场景，如记录事件的发生时间、日志记录等。

CREATE TABLE example_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME
);

2. TIMESTAMP

• 定义: TIMESTAMP 类型用于存储日期和时间，精度为秒。它和 DATETIME 最大的区别在于它会受时区影响。
• 格式: YYYY-MM-DD HH:MM:SS
• 范围: 从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
• 存储: 存储的是从 1970-01-01 00:00:01 UTC 以来的秒数，会根据当前会话的时区进行转换。
• 用途: 适用于需要存储与时区相关的时间点，如记录系统事件时间、时间戳日志等。

CREATE TABLE example_timestamp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time TIMESTAMP
);

特性对比

• 时区处理:

  • DATETIME: 不处理时区，存储的是输入的时间值。
  • TIMESTAMP: 处理时区，会自动将输入的时间转换为 UTC 存储，并在查询时根据会话时区转换回本地时间。

• 范围:

  • DATETIME: 范围更广，从 1000-01-01 到 9999-12-31。
  • TIMESTAMP: 范围有限，从 1970-01-01 到 2038-01-19，受 32 位 Unix 时间戳限制。

• 存储空间:

  • DATETIME 和 TIMESTAMP 都占用相同的存储空间（5 字节）。

示例代码

下面是一个使用 JDBC 创建表并插入数据的示例，分别展示 DATETIME 和 TIMESTAMP 类型的用法：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DateTimeTimestampExample {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/yourdatabase";
    private static final String JDBC_USER = "yourusername";
    private static final String JDBC_PASSWORD = "yourpassword";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             Statement stmt = conn.createStatement()) {

            // 创建使用 DATETIME 的表
            String createTableDatetimeSQL = "CREATE TABLE IF NOT EXISTS example_datetime (" +
                                            "id INT AUTO_INCREMENT PRIMARY KEY, " +
                                            "event_time DATETIME)";
            stmt.executeUpdate(createTableDatetimeSQL);
            System.out.println("Table with DATETIME created.");

            // 插入数据到使用 DATETIME 的表
            String insertDatetimeSQL = "INSERT INTO example_datetime (event_time) VALUES " +
                                       "('2024-06-17 10:00:00'), " +
                                       "('2024-06-18 12:00:00')";
            stmt.executeUpdate(insertDatetimeSQL);
            System.out.println("Data inserted into table with DATETIME.");

            // 查询数据
            ResultSet rsDatetime = stmt.executeQuery("SELECT id, event_time FROM example_datetime");
            System.out.println("Data from table with DATETIME:");
            while (rsDatetime.next()) {
                int id = rsDatetime.getInt("id");
                String eventTime = rsDatetime.getString("event_time");
                System.out.println("ID: " + id + ", Event Time: " + eventTime);
            }

            // 创建使用 TIMESTAMP 的表
            String createTableTimestampSQL = "CREATE TABLE IF NOT EXISTS example_timestamp (" +
                                             "id INT AUTO_INCREMENT PRIMARY KEY, " +
                                             "event_time TIMESTAMP)";
            stmt.executeUpdate(createTableTimestampSQL);
            System.out.println("Table with TIMESTAMP created.");

            // 插入数据到使用 TIMESTAMP 的表
            String insertTimestampSQL = "INSERT INTO example_timestamp (event_time) VALUES " +
                                        "('2024-06-17 10:00:00'), " +
                                        "('2024-06-18 12:00:00')";
            stmt.executeUpdate(insertTimestampSQL);
            System.out.println("Data inserted into table with TIMESTAMP.");

            // 查询数据
            ResultSet rsTimestamp = stmt.executeQuery("SELECT id, event_time FROM example_timestamp");
            System.out.println("Data from table with TIMESTAMP:");
            while (rsTimestamp.next()) {
                int id = rsTimestamp.getInt("id");
                String eventTime = rsTimestamp.getString("event_time");
                System.out.println("ID: " + id + ", Event Time: " + eventTime);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，演示了如何使用 DATETIME 和 TIMESTAMP 创建表并插入数据。请根据需要调整数据库连接字符串、用户名、密码和 SQL 语句。

总结

• DATETIME: 不处理时区，适用于存储特定时间点而不考虑时区的场景，范围从 1000-01-01 到 9999-12-31。
• TIMESTAMP: 处理时区，适用于存储与时区相关的时间点，范围从 1970-01-01 到 2038-01-19。