How do you set a default value for a MySQL Datetime column?

In modern versions of MySQL (5.6 and above), you can set a default value for a DATETIME column using CURRENT_TIMESTAMP directly in the table definition. This makes inserting rows more convenient, as the database will automatically assign the current date and time if no explicit value is provided.

1. Creating a Table with a Default `DATETIME` Value

Below is an example of creating a table with a DATETIME column (created_at) set to CURRENT_TIMESTAMP by default:

CREATE TABLE example_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DEFAULT CURRENT_TIMESTAMP: Automatically sets created_at to the current date and time for new rows, if no other value is specified.

1.1 Adding `ON UPDATE CURRENT_TIMESTAMP`

If you also want MySQL to update the created_at column to the current timestamp whenever the row is modified, use:

CREATE TABLE example_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL 
              DEFAULT CURRENT_TIMESTAMP 
              ON UPDATE CURRENT_TIMESTAMP
);

This feature is particularly helpful for audit tracking, letting you know when a row was last updated.

2. Altering an Existing Table to Add the Default Value

If your table already exists, you can modify the DATETIME column to set the default:

ALTER TABLE example_table 
MODIFY created_at DATETIME NOT NULL 
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP;

Again, the ON UPDATE CURRENT_TIMESTAMP part is optional. Include it only if you want the column to auto-update whenever the row changes.

3. Considerations

MySQL Version: The syntax DATETIME DEFAULT CURRENT_TIMESTAMP is fully supported in MySQL 5.6+ and MariaDB 10.0+ (with some variations in older versions).
Auto-Update Behavior: If you only want the creation timestamp and never want it auto-updated, skip the ON UPDATE CURRENT_TIMESTAMP clause.
Precision: MySQL and MariaDB versions supporting fractional seconds let you define precision (e.g., DATETIME(3) for millisecond accuracy).
TIMESTAMP vs. DATETIME: Historically, TIMESTAMP had better native support for default CURRENT_TIMESTAMP. But since MySQL 5.6, DATETIME gained most of those same features.

4. Improve Your Database Skills

To master more advanced database techniques—like indexing strategies, performance tuning, and schema design—check out these courses from DesignGurus.io:

Grokking SQL for Tech Interviews – Sharpen your query-writing and problem-solving skills with real-world examples.
Grokking Database Fundamentals for Tech Interviews – Dive deeper into normalization, indexing, transactions, and other crucial topics for building scalable systems.

5. Mock Interviews with Ex-FAANG Engineers

If you’re preparing for a role that requires strong database or system design skills, consider Mock Interviews by DesignGurus.io. You’ll get personalized feedback and learn how to tackle technical and behavioral questions with confidence.

Conclusion

Setting a default value for a MySQL DATETIME column is straightforward in MySQL 5.6+ using DEFAULT CURRENT_TIMESTAMP. This is especially handy for tracking creation or update times automatically, without manual intervention. Remember to tailor the ON UPDATE behavior based on whether you want the timestamp to remain static after insertion or update dynamically with each row change. By combining best practices like these with ongoing learning (via courses and mock interviews), you’ll build cleaner, more maintainable databases.

CONTRIBUTOR

TechGrind