Tuesday, February 8, 2011

Timestamp from Oracle DB

In recent cache node design, there is a timestamp field in its all interface for cache data push sequence. Keeping sequence (without expensive locking) is important to guarantee cache data and db data are consistent, esp for concurrency cache write case. It is not reliable to use cache node timestamp because from client to cache node there is network latency. In other words, first request might arrive at cache node later than second request. So the thought is to let caller (application server) to pass the timestamp when invoke cache node. In production, all application servers should be running NTP, but it is not 100% guaranteed due to human mistake (say forgetting turn on this service). In such a clustering environment, we propose to get the timestamp from backend Oracle 10g database because cluster application servers access the same database.

Side note: our current cache node is not using write-through or write-back policy, it is kind of client control for data store and cache node.

Oracle Date & Timestamp:
Starting from Oracle 9i, there is a new date type TIMESTAMP besides well known DATE. TIMESTAMP contains second fractions (millisecond), so it satisfies cache node requirement. They are data types about representing date and time values. They have the ability to store the month, day, year, century, hours, minutes, and seconds. The problem with the DATE datatype is its granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved after Oracle introduced TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around DATE or TIMESTAMP .

Here are some sample queries from dual:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual;
2011-02-09 01:37:29
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff')  from dual;
2011-02-09 01:38:39.809065
select sysdate from dual;
select systimestamp from dual;
09-FEB-11 AM +00:00

-- to get milliseconds part only
select 1000*(seconds-trunc(seconds)) millisec from
(select extract(second from systimestamp) seconds from dual)

SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual;

-- there are 2 more timestamps we can use
select localTIMESTAMP from dual
08-FEB-11 PM

JDBC to get Timestamp from Oracle:
// Get a statement from the connection
Statement stmt = conn.createStatement();

// Execute the query
ResultSet rs = stmt
.executeQuery("SELECT sysdate, systimestamp FROM dual");

// Loop through the result set
while (rs.next()) {
Console Output:

2011-02-09 01:12:25.0
2011-02-09 01:12:25.47468
iBatis SQLMap:
<select id="getDbSysDate" resultClass="java.util.Date">
   select sysdate from dual

<select id="getDbSysTimestamp" resultClass="java.sql.Timestamp">
   select systimestamp from dual

No comments:

Post a Comment