Zend_Db and Date Based Queries

Question

I'm using Zend_Db to query a table. I have some WHERE clauses that need to be dates and it's not clear (at first glance) how to do this "the right way". Does Zend_Db provide an abstraction so I don't need to concern myself with how the backend (Oracle, MySQL, etc.) expects its dates? Or do I need to format things in the way my backend expects them.

I'm trying the following with an Oracle based database (PO_DATE is a timestamp field)

$table = $this->getDbTable();    
$select = $table->select()->where('1 = ?', 1);  
$select->where('PO_DATE = ?', '2009-12-02');

and I get the following exception

  <b>Message:</b> 1843 ORA-01843: not a valid month SELECT "TABLE_NAME".* FROM "SYSTEM"."TABLE_NAME" WHERE (1 = 1) AND (PO_DATE = *'2009-12-02')  </p>

I realize I could do something like this

$date = new Zend_Db_Expr(
"to_date('2009-12-02', 'YYYY-MM-DD')"
);
$select->where('PO_DATE = ?', $date);

but that ties me to Oracle's date based Expressions, which partially defeats the purpose of using a SQL abstraction layer.

Is there a generic way to do date queries independent of the back-end implementation with Zend_Db?

Answer 1

Based on the fact I don't see any kind of contract in

abstract class Zend_Db_Adapter_Abstract

that implies "convert dates into a universal format" functionality, I assume the answer to my question is no, but with a large framework one never knows, and it seems like the kind of thing ripe for an abstraction.

Answer 2

I would definitely like such a capability, however, I haven't found any in the Zend Framework. This issue in their tracker also makes me pretty confident its not in there yet.

Answer 3

A lot of DB vendors (including Oracle) will support this format:

$table = $this->getDbTable();                           
$select = $table->select()->where('1 = ?', 1);          
$select->where('PO_DATE = ?', '02-Dec-2009');

So I tend to do that until they have better support for date/string conversion in Zend_Db