From: Adam Hardy <adam....@cyberspaceroad.com>
Subject: Re: How do I persist timestamp in UTC timezone?
To: users@openjpa.apache.org
Date: Tuesday, March 17, 2009, 5:15 PM
Hello Fay,
no reason to worry, it is logical that the time saved to
the database depends on the default time zone in Java. If
you don't set the default time zone, it uses the time in
your operating system.
I did say that the database field TIMESTAMP default is
without time zone - you can specify that it does save the
timezone, but that is an optional extra and database vendor
specific (I think).
The issue here is that Java times and dates are
time-zone-independent, because they are held as milliseconds
past 1970-01-01 GMT/UTC
As Paul said, your database stores time-zone-dependent
values (i.e. times without a timezone) and the time zone
that they are dependent on is the time zone of your
operating system, because JDBC uses that to work out what
the millisecond past 1970 value is. So if you change your OS
timezone, you change all the timestamps in your database -
as far as Java is concerned.
I figure that this not optimal, since you have to be
careful if you move a database from a host in one timezone
to a host in another timezone, but how often does that
happen?
Fay Wang on 17/03/09 23:41, wrote:
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
<t...@jotobjects.com> wrote:
From: Paul Copeland <t...@jotobjects.com>
Subject: Re: How do I persist timestamp in UTC
timezone?
To: users@openjpa.apache.org
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
<faisal.ans...@gmail.com> wrote:
From: fazi <faisal.ans...@gmail.com>
Subject: How do I persist timestamp in UTC
timezone?
To: users@openjpa.apache.org
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.
