ZF-1978: Add $_(date|time|datetime)Format private properties to Zend_Db_Adapters, let the query methods accept parameters of type Zend_Date


Followup to the ML discussion located HERE

  • All Zend_Db_Adapters should get new private properties named {{$_dateFormat}}, {{$_timeFormat}} and {{$_datetimeFormat}}. These properties should contain Zend_Date formatstrings for dates, times and datetime values appropriate for the respective database types.
  • All methods of Zend_Db_Adapters, Zend_Db_Table and Zend_Db_Select that handle parameter binding (such as, but not only, the {{Zend_Db_Adapter_Abstract::quote()}} and {{Zend_Db_Adapter_Abstract::fetch()}} families and the {{Zend_Db_Select::where()}} and {{having()}} methods, as well as the Zend_Db_Table magic setter methods) should be made capable of turning a given Zend_Date object into a date, time or datetime value appropriate to the respective database type.
  • There should be methods named {{get(Date|Time|DateTime)Format()}} to enable the user to get the Zend_Date formatstring that is stored in the Zend_Db_Adapter.
  • Setter-methods for these private properties should be explicitly prohibited.

The target of this improvement is to enhance the usability and interoperability of Zend_Date with databases, so that the user does not have to care about the right datetime value format for the database he/she is using. Also, through the formatstring getter-methods, the user should get an easy way to turn a column value which he/she knows is a datetime value back into a Zend_Date object. Setting these formatstrings must, of course, not be possible because they are database specific, and setting them to different values may lead to unexpected and unforeseeable results.


As this is database specific it would be best that each adapter know what date/time format he wants to have.

The handling within Zend_Date is quite easy...

$date = new Zend_Date($format, $dbformat);
$databasevalue = $date->toString($dbformat);

But also to mention... The fastest way would be to have all dates extracted as unix timestamp from the database... Each database also accepts timestamps as input for datetime fields. This would mean that no parsing has to occur which would make the code much faster.

Additionally, it would be very handy to allow Zend_Db to automatically adjust incoming and outgoing Zend_Date objects for database-specific date object fields, and also automatically adjust them to GMT and back.

Assigning to [~bkarwin] to initiate issue review.

Thomas said: "Each database also accepts timestamps as input for datetime fields." You have to rememer that timestamp to date conversion rely on the timezone of database. If I'm going to use different timezone that the server is set to and I want to keep dates in that timezone in my DB - I cannot use db timestamp conversion. Of course if I don't care what's inside the tables, I can use server timezone for all conversions, but that's inconvient if I would like to view/edit the data using some external app like phpMyAdmin - I need to remember the timezone.

It would be also great to have a method to easily create Zend_Date objects from the database field, like:

Then the Zend_Db_Adapter_Abstract would have:

Class will be reworked by Simon... sorry if I confused anybody. He has a better overview over the complete Db design, not only Zend_Db_Select and will help better than me.

Please categorize/fix as needed.

Resetting 'fix version priority' and 'fix version' to be re-evaluated for next release.

This issue has gone unaddressed for too long. I'm re-assigning to Ralph for re-evaluation and categorization.

I think this is out of scope for a normal issue and needs to be addressed in its own proposal. If others agree, I will try to get a proposal started instead, and will close this issue.

Issues should not be closed for a proposal.

You should add a proposal, link from the proposal to the issue as reference. And additionally add a comment to the issue with a link to the proposal.

The issue itself should be closed as soon as your proposal is completly finished and ready for core. Issues are better searchable and people tend first to look at issues than at proposals. This hsa also the effect that your proposal will be more reviewed as people finding this issue will also look at your proposal.

What's the current status of this issue? It started in 2007 but it is still open. Did I miss something?

Will it be addressed in 2.0?

Yes, this is something that will be addressed in 2.0. More aptly described as a way to cast between datatypes. Keep an eye out here: http://framework.zend.com/wiki/display/…


Can this issue be closed and/or shifted to ZF2 JIRA project? or are there plans to implement it in ZFv1?

Closing as "Won't Fix" for ZFv1. Please reopen if this is incorrect.