Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Developers List" <torque-dev@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
From: "Joerg Friedrich" <friedj@users.sourceforge.net>
To: "'Apache Torque Developers List'" <torque-dev@db.apache.org>
Subject: AW: Date and Timestamp problems
Date: Fri, 25 Feb 2005 18:05:55 +0100
Message-ID: <000001c51b5c$44fefd30$6502a8c0@gandalf>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Importance: Normal
In-Reply-To: 
 <OFAD166450.8872B6D9-ONC1256FB3.004F8979-C1256FB3.0050F281@seitenbau.net>

Hi Thomas,

you are right that MySQL works fine with the current implementation, =
since
it accepts the provided timestamp string even for date fields =
(YYYY-MM-DD
HH:MM:SS). The default implementation for the date formatting function =
in
the adapter is {ts ....}, which some database systems accept for =
TIMESTAMP,
but most likely not for DATE fields. In the case of Interbase/Firebird, =
a
query on DATE fields thus crashes.=20

There is a huge variety of what database systems accept as date and
timestamp notation. My suggestion was to clearly distinguish, as SQL =
does in
the meantime, between DATE and TIMESTAMP types. In the case of =
Interbase, I
can only choose to patch Torque to support TIMESTAMP correctly, or to
support DATE correctly, using the adapter function. I cannot cover both
(unless of course I drop the time portion of a timestamp).

The Oracle problem goes even a little deeper, since there are x versions =
of
Oracle JDBC drivers around which behave quite different regarding the =
way
they interpret DATE and TIMESTAMP. In the long run I think the safe way =
to
go would be to have DATE really just use the date portion, and TIMESTAMP
date and time portion. Most database vendors that I have watched move =
that
way. Even MySQL which was quite tolerant in this matter has with their
latest release begun to distinguish more clearly between these two SQL =
data
types.

On another note, I have extended the DBFactory class and written a new
adapter for the Firebird/Jaybird database based on the 3.1.1 release. =
The
current support for Interbase/Firebird is a little bit outdated. Are you
interested in these files? If yes how should I submit them?

Joerg


-----Urspr=FCngliche Nachricht-----
Von: Thomas Fischer [mailto:fischer@seitenbau.net]=20
Gesendet: Freitag, 25. Februar 2005 15:44
An: Apache Torque Developers List
Betreff: RE: Date and Timestamp problems


Hi Joerg,

the only problem I am aware of is that Oracle Timestamps are not =
supported
at the moment (i.e. date is used instead, and rounded to day accuracy).
There is already an open issue in scarab about this
(http://issues.apache.org/scarab/issues/id/TRQS263), which will be =
addressed
in the next release. You can change the mapping from Date to timestamps =
in
the oracle db.props file if you want to fix it yourself. If you do that,
timestamps have at least millisecond accuracy.

I have heard that mysql timestamps are ok (at least millisec accurancy),
correct me if I'm wrong.

I do not know anything about interbase and MSSQL. Can you enlighten me =
there
?

     Thomas

J=F6rg Friedrich <joerg.friedrich@emron.de> schrieb am 25.02.2005 =
14:59:20:

> Hi,
>
> we have had problems with date fields and timestamps when working with =

> different database systems. In our application we support for example=20
> Interbase, MySQL, Oracle and MS SQLServer. The adapter class of Torque =

> as far as I could see has a method for the date format. However, there =

> is no method for the timestamp format. This format may differ for=20
> different database systems/JDBC drivers. Would it be possible to add a =

> suitable
method
> in a future release?
>
> Thanks.
>
> Joerg
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org