INSERT NOW() into database row with CodeIgniter's query builder

INSERT NOW() into database row with CodeIgniter's query builder

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

Stefan Bogdanescu

Founder & Senior Architect

Handling SQL Functions in CodeIgniter Query Builder

When working with database insertions in CodeIgniter, a common stumbling block arises when you need to store the current timestamp directly from the database engine rather than calculating it in your application logic. You might naturally attempt to pass the MySQL NOW() function directly into the data array:

$data = array(
    'name' => $name,
    'email' => $email,
    'time' => NOW()
);
$this->db->insert('mytable', $data);

While this approach works in raw SQL or PDO prepared statements where the driver handles type coercion gracefully, CodeIgniter's Active Record (the predecessor to the Query Builder) takes a different stance. The framework aggressively escapes all values within the data array to prevent SQL injection. Consequently, NOW() gets treated as a literal string and wrapped in single quotes, resulting in invalid SQL syntax like 'time' => 'NOW()' instead of executing the function call.

The standard solution involves bypassing the automatic escaping mechanism for that specific column by calling set() with the third parameter set to false:

$data = array(
    'name' => $name,
    'email' => $email,
);
$this->db->set('time', 'NOW()', false);
$this->db->insert('mytable', $data);

This works effectively, but it requires managing the data payload outside of a simple associative array and introduces a slight disconnect between your data structure and the SQL generation. If you are looking for an alternative that avoids the explicit set() call while still constructing your payload in a single array step, there isn't a direct configuration flag to disable quoting globally for a specific value without affecting others.

However, you can achieve a similar result by leveraging the $bind feature of the Query Builder or utilizing raw SQL fragments if you are comfortable mixing approaches. In modern PHP environments, relying on the native DateTime class is often preferred over injecting NOW() into the database layer entirely. By passing a DateTime object to the insert method, CodeIgniter handles the serialization and escaping correctly, storing the timestamp in a standard format like Y-m-d H:i:s. This eliminates the need for any database-side function calls during insertion:

$data = array(
    'name' => $name,
    'email' => $email,
    'time' => new DateTime()
);
$this->db->insert('mytable', $data);

This approach ensures data integrity and simplifies the logic by offloading time formatting to a standard PHP class. It is also less prone to version-specific issues that might arise with different MySQL configurations or timezone settings on the server side.

If you strictly require the NOW() function for consistency with legacy database triggers or specific stored procedure dependencies, the set() method remains the most reliable path within the Query Builder context. There is no native syntax in the array payload itself—such as appending a boolean flag to the value list—that tells the builder to treat an element as raw SQL without escaping. The third parameter of set() is the intended mechanism for this specific trade-off: sacrificing a clean data structure to gain precise control over SQL generation.

For projects requiring more complex query building or if you find the Active Record patterns limiting, migrating to Laravel can offer a more robust experience. Laravel's Eloquent ORM and Query Builder provide similar functionality with enhanced safety features and better handling of raw expressions via RawExpression classes. You can explore further resources on how different frameworks handle these scenarios at https://laravelcompany.com, though the underlying principle of escaping versus raw SQL remains consistent across the ecosystem.

Our network