How do I write my query to get data based on date?

Question

Given that the date stored is UTC = 0 then how do I get all records with the date of today?

SELECT * FROM table Where datefield = CURDATE()

won't work because the stored value is the day before.

So another way of asking this is - how do I add the timezone offset to the datefield field to retrieve records that match that day?

Mat

Answer 1

You can do this by using JDate to build your query.

$date = new JDate(); //this returns NOW in UTC
// We use JDate::format instead of toSQL because we aren't using a datetime data type
$query->select('*')->from('table')
    ->where('datefield = '.$db->quote($date->format('Y-m-d')));

I hope this helps =^D Happy Joomla!ng