Keywords: SQLite | Timestamp | CURRENT_TIMESTAMP | Database Design | Default Value
Abstract: This article provides an in-depth exploration of the standard method for creating timestamp columns with default values in SQLite databases. By analyzing common error cases, it emphasizes best practices using the CURRENT_TIMESTAMP keyword, including syntax formatting, UTC time handling mechanisms, and differences from the datetime('now') function. Complete code examples and version compatibility notes help developers avoid common pitfalls and implement reliable timestamp functionality.
Problem Background and Common Errors
In database table design, it's often necessary to add timestamp fields to track record creation times. Many developers attempt to use the DATETIME('now') function as a default value, but this approach causes syntax errors in SQLite. For example, the following SQL statement will fail during execution:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP DEFAULT DATETIME('now')
);Standard Solution: CURRENT_TIMESTAMP
Since SQLite version 3.1.0, the official recommendation is to use the CURRENT_TIMESTAMP keyword as the default value for timestamp columns. This keyword is specifically designed for DEFAULT clauses and automatically inserts the current UTC date and time.
Syntax Specification and Implementation Details
The correct table creation statement should follow this format:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CURRENT_TIMESTAMP generates text values in the format "YYYY-MM-DD HH:MM:SS", representing the current UTC time. This standardized format ensures consistency and readability of time data.
Version Compatibility Notes
This feature requires SQLite version 3.1.0 or higher. Developers can verify their current database version by executing SELECT sqlite_version(); to ensure environment compatibility.
Alternative Approach Comparison
While other implementation methods exist, such as using datetime('now','localtime'), CURRENT_TIMESTAMP serves as the official standard solution with better maintainability and cross-version stability. The consistent use of UTC time also avoids complexities that may arise from timezone conversions.
Practical Recommendations
In practical development, it's advisable to always use CURRENT_TIMESTAMP as the default value for timestamps. For scenarios requiring local time, perform timezone conversions during queries rather than at the table structure level, thus maintaining data storage consistency.