Description

I like the database abstraction layer so far, but what really seems missing is proper date/time abstraction, and that's an area where database systems can be quite different and abstraction would be extremely welcome.

Bill: Sounds great!

Comments

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:22:39.000+0000

Changing fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2007-01-03T13:04:33.000+0000

Hi Ron, we are finally looking at this issue. One new component we are developing for Zend Framework is called Zend_Date. This is a very sophisticated class for handling dates, with special attention to locale support.

What I would suggest is that each Zend_Db_Adapter class have a method to convert an object of Zend_Date into a string in the format appropriate for a date literal in the respective RDBMS back-end. For example, MySQL prefers 'YYYY-MM-DD HH:MM:SS' format.

Or else it could be done "automatically", such that the query() method discovers if any of its bound parameters are instances of the Zend_Date class, and converts them to the correct string format internally before sending them to the RDBMS back-end.

Both solution can coexist. You can supply either a Zend_Date or a string for a bound parameter for a date column. So if you don't like the way Zend_Db converts the Zend_Date object to a string, you can do it yourself and pass a string formatted how you like.

What Zend_Db cannot easily do is to enforce that Zend_Date objects are used only in contexts for SQL date columns. To do this, Zend_Db would need to be able to evaluate the semantics of the SQL statements, and this is beyond the scope of Zend_Db.

Zend_Db can convert Zend_Date objects to a string representation, but it must assume that the user has used that value in an appropriate context.

Posted by Bill Karwin (bkarwin) on 2007-01-03T13:18:07.000+0000

Regarding fetching date values, a string date/time value could be used in the constructor of a Zend_Date object:

This solution works with all fetch modes, and both Zend_Db_Statement and Zend_Db_Row.

Posted by Joseph Wilk (joesniff) on 2007-02-07T04:18:20.000+0000

Might be missing something with the fetching code mentioned. I'm implementing a data abstraction layer and have adopted the approach for SQL statements mentioned.

My problem comes from how I take date results from the database and turn them into a Zend_Date.

As far as I see this the Zend_Date needs to know the format of the date in order to extract the values.
$myZendDateObject = new Zend_Date( $result['dateColumn'] );

Even if you pass in a date format string ('dMy h:m:s') you are unable to accurately break a string date up.
ie. 1112006 (is this 1st of 11th month of 11th of 1st month. Even with a format string you cannot identify this instance. Perhapes its perdantic point as you will not recieve a date of that format from the DB engine.

I've started to think going down the road of a MYSQL_Date, MSSQL_Date objects which I pass into Zend_Date and it can use the object to extract the values.

Posted by Joseph Wilk (joesniff) on 2007-02-07T07:40:01.000+0000

I've played around with the Zend_Date and note that my problem is that Zend_Date cannot tell what type the date is. You have to explicity pass the part (ie. Zend_Date::ISO_8601). Hence theres a need to tell Zend_Date what the Db is and what format its date is coming out as.

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.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:58.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-03-25T22:06:44.000+0000

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

Posted by Wil Sinclair (wil) on 2009-01-06T14:25:52.000+0000

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

Posted by Ralph Schindler (ralph) on 2009-01-09T09:45:38.000+0000

Can someone please comment as to whether the Zend_Db_Expr and Zend_Date components solve this need?

If not, I will close this within 1 week.

Thanks,
Ralph

Posted by Thomas Weidner (thomas) on 2009-01-09T10:01:46.000+0000

There are two ways:

1.) Use ISO constants as described by Bill

$date = new Zend_Date($result['datecolumn'], Zend_Date::ISO_8601);

This supports all known default date formats from databases.

2.) Use timestamp as output

$date = new Zend_Date($result['timestampcolumn']);

This issue should be closed in favor of ZF-1978.

Greetings
The Zend_Date-Master :-)

Posted by Cornelius Weiss (nelius) on 2009-01-09T10:06:32.000+0000

This issue has to be solved in the Zend_Db_Adapter, as it is DB brand dependend!

In any case, the adapters need to know the date(time) representations of the specific brand!

On one hand the adapter should autoconvert Zend_Dates into the required representation. In the case the DB supports connection Timezones, also the Timezone should be adjusted.

On the other hand in fetch mode, dates should optionally be converted to Zend_Dates by the adapter.

Posted by Thomas Weidner (thomas) on 2009-01-09T10:21:45.000+0000

First: The adapter itself has not to know it's datetime representation.
Because there is no way for the adapter to change it's representation when the user give a wrong one.
Also when the adapter outputs a datetime representation Zend_Date has to know the representation and not the adapter.

Second: The adapter can impossibly autoconvert Zend_Dates into a representation nor can it handle Timezones. There is no way to get the actual used timezone of the database.

When Zend_Date uses a defined timezone it's on you to set the wished timezone on Zend_Date for the database. It can not be adjusted by the adapter.

Third: Components should be loosly coupled. It would be a bad practice to automatically return a Zend_Date object instead of a string or integer. It should be on the developer to give the wished column to Zend_Date.