Collection: filter by created_at by local time zone? (or CreatedAtStoreDate)

Collection: filter by created_at by local time zone? (or CreatedAtStoreDate)

September 4, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Collection: filter by created_at by local time zone? (or CreatedAtStoreDate)

Filtering Magento Collections by Localized Created At Date

In Magento, you can filter collections by date using SQL queries or collection methods like addAttributeToFilter(). However, when dealing with dates stored in the database (created_at), it’s important to understand that these are typically stored in UTC/GMT format. If you want to filter by a localized date, such as a store-specific time zone, things get a bit more complex.

The Issue with Created At and Store Date

Consider an invoice created at 2014-04-30 22:27:30 in UTC/GMT. When the invoice is saved to the database, this date is stored in that format. However, when you call $invoice->getCreatedAt(), it returns a Varien_Datetimetz object, which is converted to your store’s local time zone.

The difference here lies in how Magento handles dates internally versus what is actually stored in the database. The created_at field is always UTC/GMT, but when you retrieve the date from an object, it’s converted to your current locale. This conversion can lead to discrepancies when filtering collections.

Filtering by Created At Store Date

Your question centers around how to filter a collection using the localized date (getCreatedAtStoreDate()) rather than the actual stored UTC/GMT date (getCreatedAt()). The challenge is that created_at is not available for direct filtering via collection methods like addAttributeToFilter().

A common solution is to use SQL queries directly instead of relying on Magento’s out-of-the-box collection filters. You can write a custom query that selects the localized date and then filter by this field.

Here’s an example of how you might achieve this:

$dateFrom = '2014-04-01';
$dateTo = '2014-04-30';

$query = "SELECT * FROM sales_flat_invoice WHERE (TO_TIMESTAMP(created_at AT TIME ZONE 'UTC') BETWEEN TO_TIMESTAMP('$dateFrom 00:00:00' AT TIME ZONE 'UTC') AND TO_TIMESTAMP('$dateTo 23:59:59' AT TIME ZONE 'UTC'))";

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$results = $connection->fetchAll($query);

foreach ($results as $row) {
    // Process each invoice or order here
}

In this example, we use a raw SQL query to select invoices where the localized created_at date falls within our desired range. The TO_TIMESTAMP() function is used to convert the strings to timestamps in the specified time zones.

Conclusion

Filtering Magento collections by localized dates requires understanding the difference between how dates are stored and how they are presented in your application. While Magento provides methods like getCreatedAt(), these don’t directly correspond to fields in the database, which can make direct filtering difficult.

The solution lies in writing custom SQL queries that operate on the UTC/GMT date as it is actually stored in the database, but converted to the appropriate time zone for comparison. This approach allows you to filter collections by localized dates while ensuring your filters are accurate and consistent with how Magento stores data internally.

For more detailed guidance on working with dates and collections in Magento, I recommend checking out the official Magento Developer Documentation. You can also explore the Laravel Company’s resources for a broader perspective on e-commerce development: Laravel Company Blog.

Database Orders Import/Export Errors

Our network