I have been playing around with Idiorm and I couldn't make it get all of the records from the database for today and yesterday. So, the MySQL queries are:
SELECT * FROM `video` WHERE DATE(`time_stamp`) = CURDATE() # today
SELECT * FROM `video` WHERE DATE(`time_stamp`) = CURDATE() -1 # yesterday
If I understand correctly, it cannot be exactly reproduced for Idiorm, right? I also have tried the following option, which strangely did not work (returns empty array):
$today = ORM::for_table('video')->
where_lt('time_stamp', 'CURDATE()')->
where_gte('time_stamp', 'CURDATE()-1')->find_many();
Can you please correct me on how it should be done?
It's best for MySQL performance to avoid stuff that uses functions on column values. For example,
WHERE DATE(time_stamp) = CURDATE() /* slow! */
completely defeats the use of an index to look up records by time_stamp. It causes a full-table scan, which works correctly but slowly. What you want is an SQL expression that will generate an index range scan. This does that, for all time_stamp values since midnight of today's date.
WHERE time_stamp >= CURDATE()
If you want to find all the time_stamp values from yesterday, you need this SQL statement
WHERE time_stamp >= CURDATE() - INTERVAL 1 DAY
AND time_stamp < CURDATE()
That is, you want all the time stamps in the range [yesterday midnight, today midnight), which range excludes today midnight.
I think your Idiorm code has the CURDATE() - INTERVAL 1 DAY expression wrong. CURDATE() - 1 works in Oracle, but not in MySQL.