Zend Framework

Changing fix version to 0.8.0.

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.

This could either by done by the user using a static method:

$db->query('INSERT INTO myTable (dateColumn) VALUES (?)',
  Zend_Db_Adapter_Pdo_Mysql::dateString( $myZendDateObject ) );

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.

$db->query('INSERT INTO myTable (dateColumn) VALUES (?)',
  $myZendDateObject ); // Zend_Db takes care of the conversion

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.

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

$result = $db->fetchAssoc('SELECT dateColumn FROM myTable');
$myZendDateObject = new Zend_Date( $result['dateColumn'] );

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

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.
<code>
$myZendDateObject = new Zend_Date( $result['dateColumn'] );
</code>

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.

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.

Something like

$result = $db->fetchAssoc('SELECT dateColumn FROM myTable');
$myZendDateObject = new Zend_Date( $result['dateColumn'] , $zendDbAdapter->getDbDateFormat());

Thats just one possible way of handling it.

Assigning to Darby.

Even better solution than Joseph proposed would be:

$result = $db->fetchAssoc('SELECT dateColumn FROM myTable');
$myZendDateObject = $db->createDate($result['dateColumn']);

Then the Zend_Db_Adapter_Abstract would have:

return new Zend_Date( $value , $this->getDbDateFormat());

(it can be just getDateFormat() instead of getDbDateFormat() as we are already in Db context).

Much nicer than my example, but don't forget you need to have the option of having the locale

 
$result = $db->fetchAssoc('SELECT dateColumn FROM myTable');
$myZendDateObject = $db->createDate($result['dateColumn'], $locale);

Zend_Db_Adapter_Abstract

 
return new Zend_Date( $value , $this->getDbDateFormat(), $locale);

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.

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

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

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.

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.

Unassigned, and marking as postponed till 2.0

Reassigned to Ralph Schindler

Postponed to ZF2

Any news at this one?