Not sure if this solution will work for you but here is what we use.
First off you need to know the TZ of your database. In most cases it
will be kept in GMT time. Then you will also have to know what the
users current TZ is.
At this point you can write something like....
new_time(#dateProperty#, $myTimeZone$, 'GMT')
this will convert the date from myTimeZone to GMT and then insert
it. This is oracle specific but hey that is what you are using :)
Now all the of the dates in your system will be in GMT. Now you just
have to format it back to the users TZ for display. If you
presentation layer is a webapp this is a one line option that sets
the users time zone via the core jstl tags. Then use the formatDate
tag and it will convert the date to the correct TZ for you.
Hope this helps.
Nathan
On Jun 5, 2006, at 12:54 PM, Mayeul MARGUET wrote:
I actually do not think iBATIS is changing my date values. I think
it (or Oracle JDBC driver) formats dates with my default timezone
when it needs to format them, which is expectable as a Java common
convention.
My problem here is I need to keep my default timezone unchanged,
and I need to store dates and timestamps in UTC (or GMT) timezones,
which may or may not be my default.
Indeed a Calendar holds a date, a time and the timezone it applies
to, which would make it possible to pass one as a parameter to an
iBATIS insert statement, without having to deal with timezones any
further.
I did try it, but either it is not supposed to work, either I did
not find out how to do it. (I get a NullPointerException with the
naive approach to simply pass the Calendar object as a parameter's
property and not indicating anything about its type. Guess it's the
standard behaviour with unknown classes.)
Up to this point I guess your advice is to use a Calendar to forge
up a java.util.Date object so that, when formatted using my default
timezone, it would actually represent the date in UTC timezone.
This solution works well almost always, with an unfortunate
emphasis on almost. The problem here is that in timezones using
DST, there is an hour in the year that simply does not exist, and
that therefore can't be inserted in the database even with a forged
up Date. For instance, in European CET in 2006, it was March 26
02:00:00. I simply can't find a way to insert this timestamp while
leaving my default timezone alone, and I don't fancy switching at
what would look like random instants to other threads.
A workaround just occurred to me though: it is probably possible to
programmatically convert Dates to Strings and pass them as iBATIS
statements parameters, for it to forge up an SQL statement treating
these Strings as DATEs or TIMESTAMPs, therefore keeping a legacy
data typing. I guess I will investigate in that direction for now.
--
Mayeul
-----Message d'origine-----
Envoyé : lundi 5 juin 2006 18:49
Objet : Re: Using non-default timezones
iBATIS does not mess with your date values in any way that I am aware
of. What you pass in is what gets stored. If iBATIS is changing your
date values upon insertion then this would be a bug. So, it is your
responsibility to set the Date value on your object correctly
prior to
asking iBATIS to insert the information into your database. As stated
by Graeme, you will need to use the Calendar object to accomplish
this.
Brandon Goodin
On 6/5/06, Mayeul MARGUET <[EMAIL PROTECTED]> wrote:
Subject: Using non-default timezones
Hi!
I'm using iBatis to store and fetch dates and hourly timestamps in
and
from an Oracle database.
An sql-map fragment is given below as example.
I need these dates and timestamps to be stored as their UTC
values, but
when I insert a date using an iBatis insert statement, the value is
stored using my default timezone (ie: for a GMT-1 default timezone,
01/01/2006 00:00:00 UTC is stored as 12/31/2005 or 12/31/2005
23:00:00).
This is a logical default behaviour, but still not the one I need.
I did find a few sql-map examples around the net suggesting there
was an
easy way to handle timezones, but failed both to understand how to
use
them and to find a documentation on the subject of dates and
timezones
with iBatis.
Is there an official way to do it, which would save me the hassle of
modifying my default timezone or storing my dates and timestamps as
text?
Could you point me to a documentation covering the subject?
Or do you think I am missing something and should investigate towards
some Oracle configuration I'm not aware of?
Thanks!
Here is my sql-map example, kinda straightforward:
<resultMap id="NameTimestampDate.map"
class="NameTimestampDate.class">
<result property="name" column="NAME" />
<result property="timestamp" column="MY_TIMESTAMP" />
<result property="date" column="MY_DATE" />
</resultMap>
<select id="getNameTimestampDateByName"
resultMap="NameTimestampDate.map"
parameterClass="java.lang.String">
<![CDATA[
SELECT * FROM TEST_TIMESTAMPS
WHERE NAME = #name#
]]>
</select>
<insert id="insertNameTimestampDate"
parameterClass="NameTimestampDate.class">
<![CDATA[
INSERT INTO TEST_TIMESTAMPS (NAME, MY_TIMESTAMP,
MY_DATE)
VALUES (#name#, #timestamp#, #date#)
]]>
</insert>
--
Mayeul
