MySQL: How to default a MySQL date (timestamp) field to now

MySQL date/time FAQ: How do I set a MySQL/MariaDB DATE field to default to “now,” i.e., the current time?

Setting the MySQL date to “now”

Unfortunately, at the time of this writing you can’t default a MySQL DATE field to “now,” but you can get the “now” behavior with a TIMESTAMP field. When you define a field in a MySQL database table, the syntax to create a TIMESTAMP “now” field is:

last_changed timestamp not null default now(),

where:

• last_changed is the name of my field,
• timestamp is the type of field, it can’t be null, and
• the date/time default is now()

Now when you do a MySQL INSERT, just skip this field in your SQL INSERT statement, and this field will default to the current date/time.

An example

To be clear about how this works, here’s a complete example of how to default a MySQL timestamp field to now:

mysql> create table test (foo int, ts timestamp default now());
Query OK, 0 rows affected (0.20 sec)

; describe the table
mysql> desc test;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| foo   | int(11)   | YES  |     | NULL              |       |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.02 sec)

mysql> insert into test (foo) values(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+------+---------------------+
| foo  | ts                  |
+------+---------------------+
|    1 | 2010-12-15 14:20:59 |
+------+---------------------+
1 row in set (0.02 sec)

More information

I wrote more details about this in my article, How to default a MySQL timestamp field to the current date and time.

MySQL “now” summary

In summary, if you needed to see how to get a MySQL timestamp field to “now,” I hope this example has been helpful.