Filtering Magento Collections by Created Times
In Magento, filtering collections based on created times can be a bit tricky due to the way dates are handled. Let’s walk through how you can filter your custom collection to show entries created “yesterday.”
Your Current Setup
You’ve already set the created_time field in your controller using:
setCreatedTime(Mage::getModel('core/date')->gmtDate());
And you’re trying to create a collection with products from yesterday like this:
$collection = Mage::getModel('things/things')->getCollection();
The Challenge
You’ve tried several ways to filter the created_time field, but none of them are working as expected. Let’s look at your attempts:
- Using
strtotime('yesterday'):
$collection->addFieldToFilter(
'created_time',
array(
'gt' => Mage::getModel('core/date')->date('Y-m-d H:i:s', strtotime('yesterday'))
)
);
This doesn’t work because strtotime('yesterday') returns the timestamp for yesterday at midnight, which is earlier than any product created today.
- Using relative dates:
$collection->addFieldToFilter(
'created_time',
array(
'gt' => Mage::getModel('core/date')->date('Y-m-d H:i:s', strtotime('-1 day'))
)
);
This is closer, but it still doesn’t work because the created_time field is stored as a Unix timestamp in UTC format. Magento’s date functions don’t handle this properly.
- Using from/to dates:
$fromDate = date('Y-m-d H:i:s', strtotime('-1 day'));
$toDate = date('Y-m-d H:i:s');
$collection->addFieldToFilter(
'created_time',
array(
'from' => $fromDate,
'to' => $toDate
)
);
This looks promising, but it doesn’t work because the created_time field is in UTC and your local date functions are returning dates in your current time zone.
The Solution
To filter by yesterday’s entries, you need to calculate the start of yesterday in UTC format. Here’s how:
$yesterdayStart = Mage::getModel('core/date')->timestamp(time() - 86400) . ' 00:00:00';
$collection->addFieldToFilter(
'created_time',
array(
'from' => $yesterdayStart,
'to' => time(),
'datetime' => true
)
);
Here’s what we’re doing:
- We subtract 86400 seconds (24 hours) from the current timestamp.
- We convert this to a date string in UTC format, including the time as “00:00:00”.
- We use
addFieldToFilterwith the calculated start of yesterday and the current time.
Conclusion
Filtering by created times in Magento requires careful handling of dates, especially when dealing with UTC timestamps. Knowing how to calculate date ranges properly is crucial for creating accurate collections.
Remember, you can always refer back to our guide on Magento collection filters for more examples and best practices.
Happy coding! Let me know if you have any other questions.