To my knowledge, as far as DB2 is concerned, DB2 v9 and below does not have
"timestamp with time zone" data type. The value stored in the DB2 timestamp
column is without time zone information. It is up to the application to
determine the time zone. For example, in my db2 table, I have the following
value
2009-03-17-22.05.37.569000
stored in my timestamp column.
If I set the default time zone to UTC in my application, I get the value back
as:
Created time : Tue Mar 17 22:05:37 UTC 2009
If I did not set the default time zone to UTC, I get this value:
Created time : Tue Mar 17 22:05:37 PDT 2009
A new data type "Timestamp with time zone" may be introduced in the next DB2
release, but currently there is no way to store the time zone information in
the timestamp column. DB2 experts, please correct me if I am wrong.
Having said that, with this statement,
dt.setCreatedTime(Calendar.getInstance(TimeZone.getTimeZone("Etc/UTC")));
when application sets default time zone to UTC, the timestamp value in the
database becomes "2009-03-17-23.26.53.320000". Without setting the default time
zone, the timestamp value in the database is "2009-03-17-16.23.27.494000". Let
me try a simple POJO test case to see if this is an openjpa problem or not.
--- On Tue, 3/17/09, Paul Copeland <[email protected]> wrote:
> From: Paul Copeland <[email protected]>
> Subject: Re: How do I persist timestamp in UTC timezone?
> To: [email protected]
> Date: Tuesday, March 17, 2009, 3:04 PM
> Of course java.util.Date is already measured in milliseconds
> UTC without regard to TimeZone. So it may seem that you are
> converting your Date objects to a different timezone, but
> that's not the case. This is why you can use
> Calendar.compareTo() with objects in different TimeZones.
>
> By definition - new Date() is the same thing as new
> Date(System.currentTimeMillis()) no matter what the default
> TimeZone!
>
> The link cited by Fazi implies that you have to store the
> TimeZone along with the date if you want to load the date
> (milliseconds) back into a Calendar object representing that
> TimeZone.
>
> If you change the default timezone to UTC (or Moscow, etc.)
> then all the other Calendar objects that are meant to
> represent the default Locale will be wrong!
>
> - Paul
>
> On 3/17/2009 2:15 PM, Fay Wang wrote:
> > Hi Fazi,
> > I found that by putting
> >
> TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));
> >
> > to make your java app in UTC time zone (see below
> in testDate), openjpa will store the dates in UTC in the
> database.
> > public void testDate(){
> >
> TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));
> >
> > DateTest dt = new DateTest();
> > dt.setId(id);
> >
> dt.setCreatedTime(Calendar.getInstance(TimeZone.getTimeZone("UTC")));
> > dt.setStartTime(new Date());
> >
> >
> >
> > -Fay
> >
> >
> > --- On Tue, 3/17/09, fazi
> <[email protected]> wrote:
> >
> >
> >> From: fazi <[email protected]>
> >> Subject: How do I persist timestamp in UTC
> timezone?
> >> To: [email protected]
> >> Date: Tuesday, March 17, 2009, 9:56 AM
> >> Hi
> >> I need to persist all timestamps in UTC timezone.
> I found
> >> this documentation
> >> which talks about using Calendar by setting the
> timezone
> >> value to the
> >> desired timezone
> >>
> (http://openjpa.apache.org/builds/1.1.0/apache-openjpa-1.1.0/docs/manual/ref_guide_pc_scos.html#ref_guide_pc_calendar_timezone),
> >> plus this JIRA
> >>
> (https://issues.apache.org/jira/browse/OPENJPA-322) that
> >> gives me some idea on how I can initialize
> Calendar to
> >> insert/update time in
> >> UTC but nothing has worked so far. The timestamps
> are
> >> always entered and
> >> read in DB local timezone (PDT). I noticed that
> the
> >> Calendar timezone of the
> >> retrieved object is set correctly to UTC, however,
> the time
> >> is still in
> >> local timezone (PDT). I also noticed that the
> retrieved
> >> object has JPA
> >> implementation of the Calendar object:
> >>
> org.apache.openjpa.util.java$util$GregorianCalendar$proxy.
> >>
> >> We are using openJPA version 1.1.0.
> >> I am copying my artifacts below. The test class
> has two
> >> timestamp fields,
> >> one is for UTC timezone and the other one is for
> local
> >> timezone to show that
> >> both values are same.
> >> Please let me know if any other information can
> help
> >> understand this problem
> >> better.
> >> Any help on this matter will be greatly
> appreciated.
> >>
> ---------------------------------------------------------
> >> TestDate table:
> >> CREATE TABLE DATETEST (
> >> ID VARCHAR(255) NOT NULL,
> >> CREATEDTIME TIMESTAMP,
> STARTTIME TIMESTAMP)
> >>
> ----------------------------------------------------------
> >>
> >>
> ----------------------------------------------------------
> >> JPA class:
> >>
> ----------------------------------------------------------
> >> package com.my.package.entity;
> >>
> >> import java.io.Serializable;
> >> import java.util.Calendar;
> >> import java.util.Date;
> >> import java.util.TimeZone;
> >>
> >> import javax.persistence.*;
> >>
> >>
> >> @Entity
> >> @Table(name="DATETEST")
> >> public class DateTest implements Serializable {
> >>
> >> @Id
> >> @Column(name="ID")
> >> private String id;
> >>
> >> @Temporal(TemporalType.TIMESTAMP)
> >> @Column(name="CREATEDTIME")
> >> private Calendar createdTime =
> >>
> Calendar.getInstance(TimeZone.getTimeZone("UTC"));
> >>
> >> @Temporal(TemporalType.TIMESTAMP)
> >> @Column(name="STARTTIME")
> >> private Date startTime;
> >>
> >> public Calendar getCreatedTime() {
> >> return createdTime;
> >> }
> >>
> >> public void setCreatedTime(Calendar createdTime)
> {
> >> this.createdTime = createdTime;
> >> }
> >>
> >> public Date getStartTime() {
> >> return startTime;
> >> }
> >>
> >> public void setStartTime(Date startTime) {
> >> this.startTime = startTime;
> >> }
> >>
> >> public String getId() {
> >> return id;
> >> }
> >>
> >> public void setId(String id) {
> >> this.id = id;
> >> }
> >> }
> >>
> ----------------------------------------------------------
> >>
> >>
> ----------------------------------------------------------
> >> JUnit test:
> >>
> ----------------------------------------------------------
> >> @Test
> >> public void testDate()
> >> {
> >> DateTest dt = new DateTest();
> >> dt.setId(id);
> >>
> dt.setCreatedTime(Calendar.getInstance(TimeZone.getTimeZone("UTC")));
> >> dt.setStartTime(new Date());
> >>
> >> try
> >> {
> >> //persist
> >> }
> >> catch (Exception e)
> >> {
> >> fail(e.getMessage());
> >> }
> >> // Check result
> >> DateTest returned = null;
> >> try
> >> {
> >> returned = //find by id;
> >> Calendar createdTimeC =
> returned.getCreatedTime();
> >> System.out.println("createdTime type
> :
> >> " +
> >> createdTimeC.getClass().getName());
> >> System.out.println("createdTime
> timezone:
> >> " +
> >> createdTimeC.getTimeZone());
> >> System.out.println("Created time
> :
> >> " +
> >> createdTimeC.getTime());
> >> System.out.println("Start time
> :
> >> " +
> >> returned.getStartTime());
> >> System.out.println("Created time
> (millisecs):
> >> " +
> >> createdTimeC.getTimeInMillis());
> >> System.out.println("Start time
> (millisecs) :
> >> " +
> >> returned.getStartTime().getTime());
> >> }
> >> catch (Exception e)
> >> {
> >> fail(e.getMessage());
> >> }
> >> }
> ----------------------------------------------------------
> >>
> >> The output is:
> >>
> >> createdTime type :
> >>
> org.apache.openjpa.util.java$util$GregorianCalendar$proxy
> >> createdTime timezone:
> >>
> sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]
> >> Created time : Tue Mar 17 09:40:39 PDT 2009
> >> Start time : Tue Mar 17 09:40:39 PDT 2009
> >> Created time (millisecs): 1237308039662
> >> Start time (millisecs) : 1237308039662
> >>
> >>
> ----------------------------------------------------------
> >> And the DB entry shows:
> >>
> >> db2> select * from DATETEST
> >>
> >> ID CREATEDTIME
> STARTTIME
> >> ---------------
> ----------------------------------
> -------------------------------------
> >> utc_test1 2009-03-16-16.14.32.380000
> 2009-03-16-16.14.32.380000
> >>
> >>
> >> 1 record(s) selected.
> >>
> >> db2>
> >>
> >> -- View this message in context:
> >>
> http://n2.nabble.com/How-do-I-persist-timestamp-in-UTC-timezone--tp2492546p2492546.html
> >> Sent from the OpenJPA Users mailing list archive
> at
> >> Nabble.com.
> >>
> >
> >
> >
> >
> >
> >
