• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 865
  • Last Modified:

Oracle TIMESTAMP WITH TIMEZONE and JDBC inserts/selects

Hi,

I'm having a problem with date and time with timezone manipulation.

There is a table in an Oracle 10g database that is created like this:
   create table eventLocalGMT (
       eventID NUMBER(5),
       localDate date,
       gmtDate TIMESTAMP WITH TIMEZONE);

The idea behind this is to be able to store data in the same table "as is" AND "GMT". For example, if an event occurs at 24/10/2005 10:00 GMT+2, the data would be stored as:
   localDate = 24/10/2005 10:00
   gmtDate = 24/10/2005 08:00 (GMT 0)


How can inserts and select queries be done in java through JDBC if I start with a java.util.Date initialized as the client's local date and time?
0
whiteeagl
Asked:
whiteeagl
  • 12
  • 7
1 Solution
 
zzynxSoftware engineerCommented:
Why don't you store the date in seconds since 1-jan-1970 00:00:00, i.e. Date.getTime()
combined with a String containing the Timezone.getID()
0
 
zzynxSoftware engineerCommented:
In that case you always have the Date in UTC
And you have the timezone to convert to. (probably to display?)
0
 
zzynxSoftware engineerCommented:
>> Why don't you store the date in seconds since 1-jan-1970 00:00:00, i.e. Date.getTime()
I meant milliseconds of course

To answer your Q:
- The PreparedStatement interface has setDate() and setTimestamp() functions
- Timestamp has a constructor: Timestamp(long milliseconds)
- ResultSet interface has a getDate() and getTimestamp() functions
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
whiteeaglAuthor Commented:
I understand how to setDate and setTimestamp but these functions do not insert data into a TIMESTAMP WITH TIMEZONE field.

For example, I have these values:
24/10/05    13:00   GMT+2
24/10/05    13:00   GMT+5
24/10/05    15:00   GMT+2
24/10/05    02:00   GMT+1

After Java to Oracle insert and conversion, the database should have:
localDate                                            gmtDate
24/10/05    13:00   GMT+2                 24/10/05    11:00
24/10/05    13:00   GMT+5                 24/10/05    08:00
24/10/05    15:00   GMT+2                 24/10/05    13:00
24/10/05    02:00   GMT+3                 23/10/05    23:00

So that upon retrieval (select statement), we can order data based on gmtDate column, but show the localDate column to the user.

Maybe we are going about this problem the wrong way, but the user requirement is quite "non-standard".
0
 
zzynxSoftware engineerCommented:
>> So that upon retrieval (select statement), we can order data based on gmtDate column
??

Doesn't this

>> localDate date,
>> gmtDate TIMESTAMP WITH TIMEZONE

conflict with

>>localDate                                            gmtDate
>>24/10/05    13:00   GMT+2                 24/10/05    11:00
>>24/10/05    13:00   GMT+5                 24/10/05    08:00
>>24/10/05    15:00   GMT+2                 24/10/05    13:00
>>24/10/05    02:00   GMT+3                 23/10/05    23:00

?
0
 
zzynxSoftware engineerCommented:
Shouldn't the column names be swapped in the 2nd part?
0
 
whiteeaglAuthor Commented:
my mistake, it should actually look like this:

localDate                           gmtDate
--------------------               -------------------------------------------------
06-APR-2005 14:34:58       06-APR-05 02.34.58.799000 PM EUROPE/LONDON
06-APR-2005 14:35:32       06-APR-05 02.35.32.877000 PM EUROPE/PARIS
06-APR-2005 14:36:06       06-APR-05 02.36.06.975000 PM AMERICA/INDIANAPOLIS
0
 
zzynxSoftware engineerCommented:
And you want it to be ordered on the last column?

Maybe interesting for you:
http://www.psoug.org/reference/datatypes.html
http://www.psoug.org/reference/timestamp.html
0
 
zzynxSoftware engineerCommented:
>> And you want it to be ordered on the last column?
Do you have that column only to sort on?
0
 
whiteeaglAuthor Commented:
>>>> And you want it to be ordered on the last column?
>>Do you have that column only to sort on?

basically yes...
0
 
zzynxSoftware engineerCommented:
Then you can also use a long column which contains the result of Date.getTime() and sort on that.
(without Timestamp with timezone problems)
0
 
whiteeaglAuthor Commented:
I think I found what I needed. What do you think about this solution?

// Connect to the database
ConnectionManager.openDataSource("jdbc:oracle:thin:@server:port:db", "user", "pwd");
Connection conn = ConnectionManager.getConnection();

// Insert values (change local system time zone for deeper testing)
String str = "INSERT INTO eventLocalGMT VALUES (?, ?)";
Timestamp ts = new Timestamp(Calendar.getInstance().getTimeInMillis());
OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(str);
pstmt.setTimestamp(1, ts);
pstmt.setTIMESTAMPTZ(2, new TIMESTAMPTZ(conn, ts, Calendar.getInstance()));
pstmt.execute();
conn.commit();

// Retreive values, sort by universal time (GMT) and show local time
Date oraLocalDate;
Date oraGmtDate;
TIMESTAMPTZ oraTz;
OracleStatement stmt = (OracleStatement)conn.createStatement();
OracleResultSet rset = (OracleResultSet)stmt.executeQuery("SELECT * from LAST_ACCESS_GMT ORDER BY LAST_HIT_GMT");
while(rset.next()){
      oraLocalDate = rset.getTimestamp(1);
      oraGmtDate = rset.getTimestamp(2, Calendar.getInstance());
      oraTz = rset.getTIMESTAMPTZ(2);
      System.out.println(oraLocalDate.toString() + "    ¦ " + oraGmtDate + "   ¦ " + oraTz.stringValue(conn));
}

conn.close();
ConnectionManager.closeDataSource();
0
 
zzynxSoftware engineerCommented:
>>      oraGmtDate = rset.getTimestamp(2, Calendar.getInstance());
>>      oraTz = rset.getTIMESTAMPTZ(2);

It's a bad idea to get the same field twice
0
 
whiteeaglAuthor Commented:
You are right, it was just for testing and seeing the different behaviours.

Does this seem just all to complicated?
0
 
zzynxSoftware engineerCommented:
>> Does this seem just all to complicated?
Yes, but if it works for you that's OK for me ;°)

As I said before:
make LAST_HIT_GMT being a long
and have
     pstmt.setLong(new Date().getTime());
for inserting.
But if yours do work...
0
 
whiteeaglAuthor Commented:
Would you see a way to keep original time zone information if we want to know in which zone it happened?

I know you can do a date difference, but performance wise, this isn't this best. Adding a VARCHAR2 field isn't really an option since it doesn't help for date conversions and easy comparisons.
0
 
zzynxSoftware engineerCommented:
>> Would you see a way to keep original time zone information if we want to know in which zone it happened?
See my 1st comment.
0
 
whiteeaglAuthor Commented:
as i said, using String is not pratical for date conversions and performance. I'll keep it the other way. Thanks.
0
 
zzynxSoftware engineerCommented:
>> String is not pratical for date conversions
??? If you know the timezone's ID, you can create the Timezone instance from it, and then you can convert any date to it.
>> and performance
??? comparing dates stored as longs (milliseconds in GMT) is ideal to order by

Anyway, thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now