INSERT current datetime with NOW() using CodeIgniter's query builder

INSERT current datetime with NOW() using CodeIgniter's query builder

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

Stefan Bogdanescu

Founder & Senior Architect

The Query Builder Limitation with NOW() in CodeIgniter 3

Attempting to store a user's registration timestamp by passing the raw string 'NOW()' directly into CI_DB::set() results in a silent failure rather than an error. When you execute $this->db->insert('doc_users') after setting the value this way, MySQL accepts the input because it is syntactically valid SQL, but your application logic treats it as a literal string. Consequently, the database stores 0000-00-00 00:00:00 (or similar null-equivalent values depending on column definitions) instead of the current timestamp.

This behavior stems from how CodeIgniter's Active Record and Query Builder classes handle parameter binding. The framework is designed to protect against SQL injection by automatically escaping values passed via set(). When you pass a variable, it gets prepared as a bound parameter. However, when you pass a literal string like 'NOW()', the query builder does not recognize it as a function call intended for execution; instead, it often treats it as a static value to be inserted or, in some configurations, escapes the parentheses and quotes, turning NOW() into a text string that fails to resolve at runtime.

To achieve an automatic insertion of the current timestamp, you must utilize CodeIgniter's native method for handling timestamp fields: $this->db->set_now(). This method is specifically designed to inject the database driver's native function for "now" without requiring you to manually type NOW() or worry about quote escaping.

For a standard MySQL setup with CodeIgniter 3, the solution involves replacing the manual set call with the dedicated helper:

$this->db->set('user_nicename', $nameVar);
$this->db->set('user_login', $emailVar);
$this->db->set('user_pass', password_hash($passwordVar, PASSWORD_DEFAULT)); // Best practice note
$this->db->set_now('user_registered'); // Correct approach for timestamp
$this->db->insert('doc_users');

By using set_now(), the Query Builder generates SQL that looks like this:

INSERT INTO doc_users (user_nicename, user_login, user_pass, user_registered) VALUES (?, ?, ?, NOW())

Notice that the timestamp field is not bound with a question mark placeholder. This allows the database engine to evaluate NOW() at the moment of insertion, ensuring the value reflects the exact second the record was created.

It is important to distinguish between using set_now() and set(). If you attempt to force the timestamp by passing 'NOW()' into set(), the framework's escaping mechanism might interfere depending on your database configuration or specific version quirks. In CodeIgniter 3.0.6, this ambiguity often leads to the literal storage of the string or a default zero date if the column is defined as NOT NULL but receives an empty string after escaping.

When working with MySQL, you might also consider using SET TIMESTAMP in the session configuration if your application requires the stored time to reflect the server's system clock rather than the client's connection time, though this is less common for standard web applications. For most use cases, relying on the database driver's built-in function via set_now() is the most robust approach.

If you are migrating from CodeIgniter 3 or looking at modern alternatives, keep in mind that framework architectures evolve. While CodeIgniter 3 maintains this specific behavior for backward compatibility, newer frameworks like Laravel often provide even more granular control over timestamps through model mutators or explicit casting attributes. If you have moved to a Laravel stack and are facing similar timestamp challenges, resources from https://laravelcompany.com can offer insights into how modern PHP frameworks handle temporal data types with greater flexibility than legacy builders.

The core takeaway is that while SQL allows NOW() as a function, the application framework's abstraction layer dictates whether that function executes at runtime or gets treated as data. Always prefer the framework-provided methods for inserting system-generated values to avoid silent data corruption.

Our network