Insert current date/time using now() in a field using MySQL/PHP

Insert current date/time using now() in a field using MySQL/PHP

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Resolving MySQL's now() Insertion Silence in datetime Fields

When inserting records into a MySQL table, the expectation that now() will automatically populate a DATETIME column often clashes with reality. Unlike some other database engines or ORM patterns where timestamp columns are auto-incremented to the current time upon creation, MySQL requires explicit handling if you want to store the insertion time in an application-controlled manner.

The specific scenario arises when attempting to insert data using raw SQL statements. If your table schema defines a column as DATETIME without a DEFAULT CURRENT_TIMESTAMP attribute, and you attempt to insert the current time via the function now(), you might encounter a situation where the row is created successfully for other fields, but the target datetime field remains populated with 0000-00-00 00:00:00.

This behavior is not necessarily a syntax error in the traditional sense of the SQL statement failing to parse; rather, it indicates that the function evaluation did not occur as expected within the context of the insert operation, or the resulting value was treated as invalid. The core issue usually stems from how the application layer handles string interpolation versus how the database engine evaluates functions within literals.

Consider the following raw SQL query often found in legacy PHP scripts:

mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())");

In this example, the now() function appears to be placed directly inside the string passed to mysql_query. While now() is a valid MySQL function, its evaluation depends on where it sits within the SQL statement. When executed via certain older PHP extensions or specific driver behaviors, if the query parser does not correctly identify the function call within the string literal context before sending it to the server, the database may treat the text "now()" as a static string value rather than an executable function. However, strictly speaking, now() without quotes should be evaluated by the SQL engine.

The confusion often arises because developers assume that placing now() in the value position of an INSERT statement is universally supported. In standard MySQL usage, INSERT INTO table (col) VALUES (now()) works perfectly fine provided the connection and query execution environment correctly pass the unquoted function name to the server. If the resulting column contains 0000-00-00, it often signals that the application failed to send the query as intended, or the specific MySQL client library version being used is mishandling the non-string value in a way that defaults to zero on error.

A critical distinction exists between using CURRENT_TIMESTAMP and now(). While both return the current date and time, CURRENT_TIMESTAMP is strictly for use in column definitions as a default value or trigger. now() is a scalar function intended for use within SQL expressions. When inserting data manually, you must ensure that the query string passed to the database driver does not inadvertently quote the function name.

If you are working with modern PHP applications using PDO instead of the deprecated mysql_* functions, the handling becomes more transparent:

$stmt = $pdo->prepare("INSERT INTO users (first, last, whenadded) VALUES (:first, :last, NOW())");
$stmt->execute(['first' => $first, 'last' => $last]);

Here, NOW() is passed as a literal SQL token within the prepared statement. The driver does not quote it as part of the bound parameters, ensuring the MySQL server evaluates it at execution time. If you were to bind the result to a parameter like :time, even if you set its value to now(), you would be inserting the string "now()" rather than the timestamp, which would then fail validation depending on strict SQL modes.

The persistence of 0000-00-00 in your specific case likely indicates that the query executed without errors but failed to populate the field because the function was not evaluated. This can happen if the underlying MySQL client library interprets the string concatenation incorrectly or if the column definition has strict mode enabled, causing an invalid insertion attempt to default to zero instead of throwing a fatal exception. Always verify your SQL Mode settings; STRICT_TRANS_TABLES will cause the query to fail with an error rather than inserting a null/zero value when a function fails to resolve correctly.

For developers maintaining legacy codebases or migrating from older frameworks, understanding that now() is not a string literal but a dynamic expression is essential. If you require more control over the timestamp generation, such as adding seconds to the current time or formatting it specifically, ensure these operations happen at the application level before binding values, rather than attempting complex function chaining within the SQL insert statement unless the database driver supports it robustly.

Our network