How to retrieve and use Oracle TIMESTAMPs in Java

A few rules about using TIMESTAMPs in Java:

1. Never make assumptions about things like the timezone in the database and/or the application server.
2. It is preferable to retrieve the TIMESTAMP (or Date) field from the database as a string using the appropriate format. You can then easily convert it to java.sql.Timestamp and use it in Java.
• If you have to compare values that are stored in the database, it is better to do it in your SELECT statement and return something appropriate (i.e. time difference) to Java.
• If you must return something to use it for a comparison in Java, then prefer to return the TIMESTAMP after you have ensured that this is converted to GMT (UTC) time.

How to do it:

• Retrieve the GMT value of a TIMESTAMP as a string that can be converted to java.sql.Timestamp. In the following example change_tm is a Timestamp filed. We are using SYS_EXTRACT_UTC to convert it to GMT.

SELECT 
     TO_CHAR(CHANGE_TM, 'yyyy-mm-dd hh24:mi:ss') CHANGE_TM,
     TO_CHAR(SYS_EXTRACT_UTC(CHANGE_TM), 'yyyy-mm-dd hh24:mi:ss') GMT_CHANGE_TM
FROM TIME_TBL
WHERE  ID = :b1;



• Convert the selected value to a java.sql.Timestamp by simply calling Timestamp.valueof().
• Compare the selected value with the current time of the application server using a Calendar. The Calendar will provide useful information like the offset from GMT.

     Calendar nowCal =GregorianCalendar.getInstance();
     System.out.println("Date now: " + nowCal.get(Calendar.YEAR) + "/" +
                         nowCal.get(Calendar.MONTH) + "/" +
                         nowCal.get(Calendar.DAY_OF_MONTH) + " " +
                         nowCal.get(Calendar.HOUR_OF_DAY) + ":" +
                         nowCal.get(Calendar.MINUTE) + ":" +
                         nowCal.get(Calendar.SECOND) +
                         " Offset from GMT " +
                         nowCal.get(Calendar.ZONE_OFFSET) +
                         " Timezone: " +
                         nowCal.getTimeZone().getDisplayName());

     long now = nowCal.getTimeInMillis();

     System.out.println("Diff before now and last change time: " +
                    ((now - rec.getGmtChangeTm().getTime() - nowCal.get(Calendar.ZONE_OFFSET)) / 1000 / 60) +
                    " minutes");