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

MySQL date 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 you can’t default a MySQL DATE field to “now,” but you can get the “now” behavior with a TIMESTAMP field. The syntax to create a MySQL 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.

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)

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)

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.

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

Add new comment

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

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.