How to create timestamp column with default value 'now'?

Quick command breakdown

In SQL, CURRENT_TIMESTAMP is commonly used to retrieve the current local date and time. It yields results in the "YYYY-MM-DD HH:MM:SS" format. SQLite started supporting CURRENT_TIMESTAMP as a default value since version 3.1.0 during table creation.

For local timezone settings in SQLite, enclose datetime('now','localtime') in parentheses in the DEFAULT clause:

CREATE TABLE your_table (
    your_column TIMESTAMP DEFAULT (datetime('now','localtime'))
);
-- Now SQLite knows where "home" is.


If a value isn't specified for the timestamp column when a record is inserted, the DEFAULT clause gets evaluated.

Timezone and Update Considerations

When handling timezones across various SQL databases, results may vary. Always use functions like GETUTCDATE() in SQL Server or UTC_TIMESTAMP in MySQL for timezone independence.

For updating timestamps, in MySQL, don't forget ON UPDATE CURRENT_TIMESTAMP for keeping a history of updates.

Be wary of data type and version constraints, such as older versions of SQLite (prior to 3.1.0), and SQL Server's preference for GETDATE() over CURRENT_TIMESTAMP.

Practical scenarios

Timestamp at record creation

During record insertion, confirm that the default time coincides with the time of creation. This usually holds in PostgreSQL and MySQL. However, SQLite and SQL Server may require additional conditions.

Time updates each time a record changes

To update the timestamp at each change, MySQL provides ON UPDATE CURRENT_TIMESTAMP. Meanwhile, PostgreSQL users might find triggers or functions more suitable.

Overriding timestamp defaults

Systems should allow manual overrides of the default timestamp when necessary.

SQL flavors and their nuances

Documentations are lifesavers. Each SQL database system handles timestamp defaults differently; hence, it's essential to understand their functions and syntax.