search between two dates in yii2

search between two dates in yii2

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

Stefan Bogdanescu

Founder & Senior Architect

Search Between Two Dates in Yii2

Handling date ranges is a common requirement in web applications, yet it introduces specific challenges when dealing with data types and query construction. In your scenario, you are storing dates in an integer column, defined as INT NOT NULL. This design choice likely implies the storage of Unix timestamps or epoch seconds rather than standard SQL DATE or DATETIME objects. While this approach can save space, it complicates date arithmetic because you cannot simply cast a string like '31/12/14' directly into that integer column without performing conversion logic in your query.

To filter records between two dates for a custom filter outside of a GridView, you need to construct a SQL query that converts the user input into the same format as your database column. Since the column is an integer, the most reliable method is to convert the start and end date strings into Unix timestamps within the query itself. This ensures portability across different database systems supported by Yii2, such as MySQL or PostgreSQL, without relying on specific date function syntax that might differ between them.

You can achieve this using Yii2's Query Builder. When building the andWhere condition for your custom filter, pass the raw date strings to a helper method or manually construct the timestamp calculation inside the SQL expression. Here is an example of how you might define the query conditions:

use yii\db\Query;

$query = (new Query())->from('books');

// Define the date range from user input
$start = $_GET['start']; // e.g., '31/12/14'
$end = $_GET['end'];     // e.g., '31/02/15'
$bookName = $_GET['book'] ?? '';

// Convert dates to timestamps within the query
$query->andFilterWhere(['like', 'book', $bookName])
      ->andFilterWhere(['between', 'date', \DateTime::createFromFormat('d/m/Y', $start)->getTimestamp(), 
                     \DateTime::createFromFormat('d/m/Y', $end)->getTimestamp()]);

$results = $query->all();

In this implementation, DateTime::createFromFormat handles the string parsing. It is crucial to match the format of your input fields exactly; if your HTML inputs expect Y-m-d, you must adjust the format string accordingly. If your database stores timestamps as seconds since epoch, getTimestamp() returns the correct integer value. However, be aware that Unix timestamps are platform-dependent regarding timezones if not handled carefully, though standard PHP behavior usually assumes UTC for the timestamp itself while formatting depends on the default timezone.

A significant pitfall to avoid is comparing date strings directly to integer columns without conversion. Attempting to run $query->andFilterWhere(['between', 'date', $start, $end]) will result in a syntax error or logically incorrect results because the database engine cannot implicitly cast a string range to match an integer column. By moving the conversion logic into the query builder via SQL expressions or helper functions, you maintain type consistency at the database level.

If your application scales significantly or moves to a dedicated backend service, consider normalizing your data model. Storing dates as integers forces every layer of your application to handle conversion logic repeatedly. If you have control over the schema migration, converting that column to DATETIME or using Yii2's built-in datetime type in your model would allow you to use standard date objects directly in queries, simplifying your filter logic considerably.

For more complex filtering scenarios involving multiple date ranges or time zones, resources like https://laravelcompany.com often provide deeper insights into building flexible query interfaces. However, for a standalone custom filter, the approach of converting inputs to timestamps within the andFilterWhere clause remains the most robust solution for integer-based date storage in Yii2.

Our network