Re: [fw-db] Storing a Zend_Date in a Zend_Db_Table

Hector,

you asked if Zend_Date can handle extended timestamps...
And the answer is YES...

That the database can not handle such timestamps has been said before.
So I don't see the reason why you now wonder about this.

Greetings
Thomas Weidner, I18N Team Leader, Zend Framework
http://www.thomasweidner.com

----- Original Message -----
From: "Hector Virgen" <djvirgen@gmail.com>
To: "Thomas Weidner" <thomas.weidner@gmx.at>
Cc: <fw-db@lists.zend.com>
Sent: Tuesday, April 07, 2009 9:40 PM
Subject: Re: [fw-db] Storing a Zend_Date in a Zend_Db_Table

Thomas,
I understand that Zend_Date allows timestamps beyond the unix timestamp
limitations, but storing those dates in the database can become a chore due
to the required interpretations. For example, this snippet of code must be
aware of a) the database being used and b) the format string supported by
the database.

$userRow = $usersTable->find(123)->current();
$date = new Zend_Date();
$date->setYear(1920);

$databaseSpecificDateFormat = ???;

$userRow->date_of_birth = $date->toString($databaseSpecificDateFormat);
$userRow->save();

Would it be possible to set up a translation layer? Perhaps not within
Zend_Date but within Zend_Db_Adapter? It would be nice to be able to do
something like this:

$userRow->date_of_birth = $date;

-Hector

On Tue, Apr 7, 2009 at 12:05 PM, Thomas Weidner
<thomas.weidner@gmx.at>wrote:

> As I said, Zend_Date doesn't have this limitation.
> You can calculate astronomical dates with Zend_Date.
> It's just a matter of your environments performance.
>
> Greetings
> Thomas Weidner, I18N Team Leader, Zend Framework
> http://www.thomasweidner.com
>
> ----- Original Message ----- From: "Hector Virgen" <djvirgen@gmail.com>
> To: "Thomas Weidner" <thomas.weidner@gmx.at>
> Cc: <fw-db@lists.zend.com>
> Sent: Tuesday, April 07, 2009 8:27 PM
>
> Subject: Re: [fw-db] Storing a Zend_Date in a Zend_Db_Table
>
>
> MySQL doesn't seem to support negative unix timestamps. Is Zend_Date able
> to
> get around this?
>
> SELECT FROM_UNIXTIME(1234567890);
> --> 2009-02-13 15:31:30
>
> SELECT FROM_UNIXTIME(-1234567890);
> --> NULL
>
> -Hector
>
>
> On Tue, Apr 7, 2009 at 8:15 AM, Thomas Weidner <thomas.weidner@gmx.at
> >wrote:
>
> As long as you use Zend_Date and a database you will not have this
>> limitation.
>> Both can handle unix timestamps which are beyond this limitation.
>>
>> Still, there is a limitation for timestamps when using databases which is
>> commonly 1900/1914 - 2038
>>
>> Greetings
>> Thomas Weidner, I18N Team Leader, Zend Framework
>> http://www.thomasweidner.com
>>
>> ----- Original Message ----- From: "Hector Virgen" <djvirgen@gmail.com>
>> To: "Thomas VEQUAUD" <thomas.vequaud@gmail.com>
>> Cc: <fw-db@lists.zend.com>
>> Sent: Tuesday, April 07, 2009 4:50 PM
>>
>> Subject: Re: [fw-db] Storing a Zend_Date in a Zend_Db_Table
>>
>>
>> My only concern with unix timestamps is their limited range. Do databases
>> support negative unix timestamps, for dates before 1970?
>> -Hector
>>
>>
>> On Tue, Apr 7, 2009 at 7:03 AM, Thomas VEQUAUD <thomas.vequaud@gmail.com
>> >wrote:
>>
>> You can define a macro MYSQL_DATEPART within the bootstrap file where
>>
>>> the date part is YYYY-MM-DD HH:MM:SS and you call your Zend_Date
>>> object with it : $date->get(MYSQL_DATEPART)
>>> But, like Paul said, it's really easier and better to use timestamp
>>> instead of a formated date.
>>>
>>> Now, it's up to you...
>>>
>>>
>>> On Mon, Apr 6, 2009 at 8:26 PM, Hector Virgen <djvirgen@gmail.com>
>>> wrote:
>>> > Would it be a step in the right direction to include format constants
>>> > >
>>> for
>>> > various db types?
>>> > For example, $date->toString(Zend_Date::MYSQL_DATETIME) could produce
>>> > '2009-04-06 11:24:37'
>>> > -Hector
>>> >
>>> >
>>> > On Mon, Apr 6, 2009 at 11:20 AM, Thomas Weidner <thomas.weidner@gmx.at
>>> >
>>> > wrote:
>>> >>
>>> >> The problem with this approach is that it is not database agnostic.
>>> >> The expression to set depends on the used DBMS.
>>> >>
>>> >> Greetings
>>> >> Thomas Weidner, I18N Team Leader, Zend Framework
>>> >> http://www.thomasweidner.com
>>> >>
>>> >> ----- Original Message ----- From: "Thomas VEQUAUD"
>>> >> <thomas.vequaud@gmail.com>
>>> >> To: "Thomas Weidner" <thomas.weidner@gmx.at>
>>> >> Cc: <fw-db@lists.zend.com>; <darylhandley@gmail.com>
>>> >> Sent: Monday, April 06, 2009 4:49 PM
>>> >> Subject: Re: [fw-db] Storing a Zend_Date in a Zend_Db_Table
>>> >>
>>> >>
>>> >> Or just :
>>> >> $paymentData = array(
>>> >> 'DATE' => new Zend_Db_Expr('SYSDATE'),
>>> >> 'USERID' => $userId,
>>> >> 'AMOUNT' => $amount
>>> >> );
>>> >>
>>> >> On Thu, Apr 2, 2009 at 7:09 AM, Thomas Weidner <thomas.weidner@gmx.at
>>> >
>>> >> wrote:
>>> >>>
>>> >>> There are two ways:
>>> >>>
>>> >>> Eighter use getTimestamp to store the date as unix timestamp. Note
>>> >>> >>>
>>> that
>>> >>> databases as also Zend_Date supports negative timestamps and >>>
>>> timestamps
>>> >>> beyond 32bit integer.
>>> >>>
>>> >>> Or use toString() and get the format you want to have manually...
>>> >>> (toString('dd.MM.yyyy HH:mm:ss') for example)
>>> >>>
>>> >>> There are issues to add dbagnostic date format to Zend_Db, but they
>>> >>> are
>>> >>> actually not solved and have not much users which voted for them.
>>> >>>
>>> >>> Greetings
>>> >>> Thomas Weidner, I18N Team Leader, Zend Framework
>>> >>> http://www.thomasweidner.com
>>> >>>
>>> >>> ----- Original Message ----- From: "Daryl Handley"
>>> >>> <darylhandley@gmail.com>
>>> >>> To: <fw-db@lists.zend.com>
>>> >>> Sent: Thursday, April 02, 2009 1:37 AM
>>> >>> Subject: [fw-db] Storing a Zend_Date in a Zend_Db_Table
>>> >>>
>>> >>>
>>> >>>> I'm trying to store a date in a db table (using Oracle) and it
>>> >>>> fails
>>> >>>> telling
>>> >>>> me.
>>> >>>>
>>> >>>> ORA-01858: a non-numeric character was found where a numeric was
>>> >>>> expected
>>> >>>>
>>> >>>> My code looks something like this
>>> >>>>
>>> >>>> $paymentData = array(
>>> >>>> 'DATE' => Zend_Date::now(),
>>> >>>> 'USERID' => $userId,
>>> >>>> 'AMOUNT' => $amount
>>> >>>> );
>>> >>>> $paymentDbTable = new PaymentDbTable();
>>> >>>> $paymentDbTable->insert($paymentData);
>>> >>>>
>>> >>>> I've also tried using Zend_Date::now()->getIso() and it doesn't
>>> >>>> like
>>> >>>> that
>>> >>>> much either (ORA-01861: literal does not match format string).
>>> >>>>
>>> >>>> I have a couple of workarounds but I don't really like either of
>>> >>>> them
>>> >>>> because they kind of tie it to Oracle (format into a string literal
>>> that
>>> >>>> Oracle likes or use a Zend_Db_Expr('SYSDATE'). In Java this was
>>> >>>> >>>>
>>> pretty
>>> >>>> simple and you just pass the date and the adapter layer figures out
>>> what
>>> >>>> to
>>> >>>> do. I've got to think there must be an easy db agnostic way to do
>>> >>>> it
>>> in
>>> >>>> Zend
>>> >>>> as well.
>>> >>>>
>>> >>>> Thanks
>>> >>>> Daryl
>>> >>>>
>>> >>>
>>> >>>
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Thomas VEQUAUD http://thomas.vequaud.free.fr/
>>> >> Expert EPITECH en Ingénierie Informatique
>>> >> Tél : +33(0)6.50.39.28.10 Fax: +33(0)9.58.46.10.07
>>> >
>>> >
>>>
>>>
>>>
>>> --
>>> Thomas VEQUAUD http://thomas.vequaud.free.fr/
>>> Expert EPITECH en Ingénierie Informatique
>>> Tél : +33(0)6.50.39.28.10 Fax: +33(0)9.58.46.10.07
>>>
>>>
>>>
>>
>