MySQL: Default a field to the current date/time

MySQL date/time FAQ: How do I create a field in a MySQL database table that will default to the current date and time whenever a new record is inserted into my table?

Answer: Just define the field in your table as a timestamp field, and combine that with the default keyword and the MySQL now() function when you define this field in your database table.

The syntax for creating a MySQL timestamp field that defaults to the current date and time when creating a new database table looks like this:

event_time timestamp not null default now()

A complete MySQL current date time default example

To put this in a little more perspective, here's the complete DDL for a small MySQL database table whose definition includes a timestamp field that defaults to the current data and time:

drop table if exists file_events;

create table file_events (
    id int unsigned auto_increment not null,
    file_id int unsigned not null,
    event_time timestamp not null default now(),    # the field that defaults to "now"
    primary key(id),
    constraint foreign key fk_file_events_to_sam_files (file_id) references files(id) on delete cascade
) ENGINE=InnoDB;

The event_time field in that table definition shows how to use the MySQL now() function.

See the now() output without creating a table

As a final note, you can see the output of the MySQL now function without creating a database table. Just enter this command from the MySQL command line interface (CLI) to see the current date and time:

select now();

Summary

I hope these MySQL current date/time examples have been helpful. If you have any questions about these examples, just use the Comment form below.

Post new comment

The content of this field is kept private and will not be shown publicly.