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

Store your timestamp in a varchar... And convert it with Zend_Date.
You're welcome!

On Tue, Apr 7, 2009 at 10:44 PM, Hector Virgen <djvirgen@gmail.com> wrote:

Sorry for the confusion, Thomas. When you said "As long as you use Zend_Date and a database you will not have this limitation" I thought you meant that the two can work together. 

The only way AFAIK to safely store a date in the database using a Zend_Date object is to manually output it to a string that the database can understand. 

I wouldn't be opposed to seeing this type of translation done at the adapter level to help ensure that dates beyond the unix timestamp are safely stored, because as I pointed out early MySQL does not support negative unix timestamps.

Adding translations to the adapters would help prevent novice programmers like myself do something like this:

$date = new Zend_Date();

$date->setYear(1920);

$userRow->date_of_birth = new Zend_Db_Expr("FROM_UNIXTIME({$date->getTimestamp()})");

-Hector

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

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

--
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