ZF-181: date/time abstraction

Issue Type: New Feature Created: 2006-07-01T09:43:50.000+0000 Last Updated: 2011-08-17T03:42:35.000+0000 Status: Postponed Fix version(s): - Next Major Release ()

Reporter: Ron Korving (abraxas) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:


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!


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.

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

<pre class="highlight">
$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.

<pre class="highlight">
$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.

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:

<pre class="highlight">
$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.

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.

Something like

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

Thats just one possible way of handling it.

Posted by Bill Karwin (bkarwin) on 2007-04-10T16:29:24.000+0000

Assigning to Darby.

Posted by Artur Jedlinski (nataniel) on 2007-09-28T00:09:29.000+0000

Even better solution than Joseph proposed would be:

<pre class="highlight">$result = $db->fetchAssoc('SELECT dateColumn FROM myTable');
$myZendDateObject = $db->createDate($result['dateColumn']);

Then the Zend_Db_Adapter_Abstract would have: ``` (it can be just getDateFormat() instead of getDbDateFormat() as we are already in Db context).

Posted by Joseph Wilk (joesniff) on 2007-09-28T09:27:40.000+0000

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

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


<pre class="literal"> 
return new Zend_Date( $value , $this->getDbDateFormat(), $locale);

Posted by Thomas Weidner (thomas) on 2008-03-11T03:35:38.000+0000

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

<pre class="highlight">
$date = new Zend_Date($result['datecolumn'], Zend_Date::ISO_8601);

This supports all known default date formats from databases.

2.) Use timestamp as output

<pre class="highlight">
$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.

Posted by Ralph Schindler (ralph) on 2010-02-12T11:15:44.000+0000

Unassigned, and marking as postponed till 2.0

Posted by Ramon Henrique Ornelas (ramon) on 2011-01-09T18:08:16.000+0000

Reassigned to [~ralph]

Posted by Ramon Henrique Ornelas (ramon) on 2011-01-09T18:10:04.000+0000

Postponed to ZF2

Posted by Handrus Stephan Nogueira (handrus) on 2011-08-17T03:42:35.000+0000

Any news at this one?

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.