ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: Using non-default timezones
Date Mon, 05 Jun 2006 19:56:04 GMT
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.

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 <mmarguet@aneo.fr> 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[
>>                         VALUES (#name#, #timestamp#, #date#)
>>                 ]]>
>>         </insert>
>> --
>> Mayeul

View raw message