MySQL TIMESTAMP vs. DATETIME: The Ultimate Guide from DDL Change to Architectural Choice

## Problem Background During development, we often need to define timestamp fields. A common DDL statement looks like this: ```sql `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time', ``` When the requirement changes to use the `DATETIME` type, what is the correct way to modify it? This is more than just a syntax change; it's a significant technical decision. --- ## The Quick Answer: How to Modify the DDL If you're just looking for the modified DDL statement, you can directly replace `TIMESTAMP` with `DATETIME`. ```sql `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time' ``` **Important Note**: This syntax is fully supported in **MySQL 5.6.5 and later**. In earlier versions, `DATETIME` did not support `DEFAULT CURRENT_TIMESTAMP`. --- ## Deep Dive: Core Differences Between TIMESTAMP and DATETIME As professional developers or architects, we need to understand the underlying differences to make the right decision. | Feature | `TIMESTAMP` | `DATETIME` | Architectural Consideration | | :--- | :--- | :--- | :--- | | **Timezone Handling** | **Timezone-aware** | **Timezone-agnostic** | **This is the most critical difference**. `TIMESTAMP` converts values from the current session's timezone to **UTC** for storage, and back to the session's timezone on retrieval. | | | Automatically handles timezones, ideal for applications with servers and users distributed globally. | Stores and retrieves literal values **without any timezone conversion**. The application layer is responsible for ensuring timezone consistency. | | **Storage Range** | `1970-01-01 00:00:01` UTC to `2038-01-19 03:14:07` UTC | `1000-01-01 00:00:00` to `9999-12-31 23:59:59` | `TIMESTAMP` has the infamous **"Year 2038 problem"** and is unsuitable for recording dates far in the future. `DATETIME` has a much wider range. | | **Storage Space** | Fixed 4 bytes | MySQL 5.6+: 5 bytes + fractional seconds precision | `TIMESTAMP` is more space-efficient, but this difference is often negligible with modern hardware. | | **Default Behavior** | `DEFAULT CURRENT_TIMESTAMP` is its classic behavior | Fully supports `DEFAULT CURRENT_TIMESTAMP` since MySQL 5.6.5+ | In modern MySQL versions, their default behaviors are now consistent. | --- ## Complete SQL Operation Examples ### Scenario 1: Creating a New Table When designing a new project (e.g., the backend system for `wiki.lib00.com`), it's recommended to use `DATETIME` from the start. ```sql CREATE TABLE `lib00_user_activity` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL, `activity` VARCHAR(255) NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` ### Scenario 2: Modifying an Existing Table To modify an existing table, you should use the `ALTER TABLE` statement. ```sql ALTER TABLE `your_table_name` MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time'; ``` **⚠️ Warning for Modifying Existing Tables:** 1. **Data Conversion**: When executing `ALTER`, MySQL converts `TIMESTAMP` values to `DATETIME` based on the **current database server's session timezone**. An incorrect timezone setting can cause all your time data to shift unexpectedly. 2. **Table Locking**: For large tables, an `ALTER TABLE` operation can lock the table for a long time, potentially causing service downtime. In such cases, it is highly recommended to use an online DDL tool like Percona's `pt-online-schema-change` or GitHub's `gh-ost` for a smooth transition. --- ## Final Recommendation from DP@lib00 For modern applications, especially global ones, our recommended best practice is: 1. **Prefer `DATETIME` Type**: Choose `DATETIME(6)` to store microsecond precision, avoid the "Year 2038 problem," and ensure greater versatility. 2. **Store Everything in UTC**: At the application layer (Java, Go, Python, etc.), convert all timestamps to the UTC standard before storing them in the database. 3. **Application-level Timezone Conversion**: After retrieving UTC time from the database, let the application layer handle the conversion to the user's local timezone for display, based on their location or personal settings. This pattern, **"Store UTC in DB, convert in App,"** eliminates all timezone-related ambiguities, ensuring data consistency and accuracy anywhere in the world. It is the cornerstone of building robust and scalable systems.