J2EE News, Forums, Discussions, Articles, Jobs, Books J2EEWorld.COM
J2EE News, Forums, Blogs, Articles, Books, Jobs 		J2EE Careers | Vancouver J2EE Jobs | Sitemap

Search >> 
 Site last updated: Monday, 06 October 2008
 Home arrow J2EE Weblog arrow Use Oracle Timestamps in Java
Main Menu
   Home
   J2EE News
   J2EE Weblog
   J2EE Books
   J2EE articles
   J2EE Web Links
   J2EE FORUM
   J2EE Download
   Newsletter
   About us
Latest News
Testing Portal Web Applications With HttpUnit
IBM vs Microsoft Developer Productivity Study
The J2EE 1.4 Tutorial
Linux file system structures for J2EE developers
Apache Jakarta DBCP Component -- efficient, feature-rich connection pool package
Login Form





Lost Password?
No account yet? Register
SoloSunglasses
SoloSunglasses: buy sunglasses direct from the manufacturer
Most Read Content
Using Oracle Timestamps in Java
IBM Restocks Eclipse Project
Introducing Sun Java Desktop System, Release 2
How to submit file to J2EE server?
Java 2 Enterprise Edition
Polls
What J2EE Application Server do you use?
 
Hit Counter
Visitors: 530641
Use Oracle Timestamps in Java
Written by Java boy   
Wednesday, 26 January 2005

Recently, I was doing a task which needs to consider avoiding multiple transactions coming from different servers interfere each other. Since we also require to record the time of the task, I proposed using Oracle Timestamp type to keep the data integrity of multiple transactions.

Below table was introduced to keep the Server_status

SERVER_NAME

LAST_PROCESS_TIME

JOB_NAME

The pseudo SQL statement which ensures the data integrity of the hold back order transaction as below:

 

UPDATE SERVER_STATUS

SET LAST_PROCESS_TIME = LOCALTIMESTAMP AND

        SERVER_NAME= my_server_name

WHERE

        LOCALTIMESTAMP - last_process_time > 1 minute AND

        TASK_NAME =”my_task”

COMMIT

IF updated_record_number != 0 THEN 

//the above logic is to avoid too many overlapping transactions, which could possibly consuming DB resources

BEGIN TRANSACTION

         GET last_process_time

        //do my job here

         //below SQL script is to ensure any transactions that longer than 1 min will be failed if there’s another server already //executed the transaction successfully

        SELECT * from SERVER_STATUS FOR UPDATE of LAST_PROCESS_TIME nowait

        UPDATE SERVER_STATUS

SET LAST_PROCESS_TIME = LOCALTIMESTAMP AND

        SERVER_NAME= my_server_name

WHERE

        LAST_PROCESS_TIME = last_process_time    AND

        SYSDATE - last_process_time > 1 minute AND

        TASK_NAME =”Hold_back_order”

         IF updated_record_number == 0 THEN

                DO ROLLBACK

         ELSE

                DO COMMIT

END TRANSACTION

The executeUpdate() method will return the number of record set that has been updated. If the number is zero, which means another application server is already processing the transaction, then this server just ignore current transaction; otherwise it shall continue the process hold back order transaction.

some sample code as below for reference. Note, the compare of timestamp is a bit tricky.

 static String TIMESTAMP_FORMAT = "dd-mon-yyyy HH.MI.SSXFF PM";

con = DriverManager.getConnection(
       "jdbc:oracle:thin:@myserver:myport:dbname", USERNAME, PASSWORD);

Statement stmt = con.createStatement();

con.setAutoCommit(false);

   ResultSet rs = stmt.executeQuery("SELECT TO_CHAR(LAST_PROCESS_TIME, '" + TIMESTAMP_FORMAT + "') last_time from server_status");
   if (rs.next())
    last_time = rs.getString("LAST_TIME");
   for (int i=0; i<count; i++) {
    stmt.executeUpdate("UPDATE TEST SET TEST_VALUE=TEST_VALUE+1");
   }
   stmt.executeUpdate("select * from Server_status FOR UPDATE of LAST_PROCESS_TIME nowait");
   
   String sql= "update Server_status set LAST_PROCESS_TIME= LOCALTIMESTAMP " +
    "where LAST_PROCESS_TIME= TO_TIMESTAMP('" + last_time + "', '" + TIMESTAMP_FORMAT+"')";
   int ret = stmt.executeUpdate(sql);
   
   if (ret == 0) {
    con.rollback();
   } else {
    con.commit();

 

Oracle Timestamps
 
Version 10.1

General

List of timezones

 set linesize 130

SELECT *
FROM v_$timezone_names;
 
CURRENT_TIMESTAMP

Current Timestamp

 CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP FROM dual;
 
DBTIMEZONE
Current Time Zone DBTIMEZONE
ALTER SESSION SET time_zone = local;

SELECT dbtimezone FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT dbtimezone FROM dual;

ALTER SESSION SET time_zone = local;
 
DUMP
Returns the number of bytes and datatype of a value DUMP(<value>)
SELECT dump(SYSTIMESTAMP) FROM dual;
 
EXTRACT
Extracts and returns the value of a specified datetime field from a datetime or interval value expression Values That Can Be Extracted:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

EXTRACT (<type> FROM <datetime | interval>)
SELECT EXTRACT(YEAR FROM DATE '2005-03-07') FROM dual;
 
FROM_TZ
Converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value FROM_TZ(<timestamp> <timestamp>)
SELECT FROM_TZ(TIMESTAMP '2003-11-20 08:00:00', '3:00')
FROM from;

SELECT FROM_TZ(TIMESTAMP '2003-11-20 19:30:00', '3:00')
FROM dual;
 
LOCALTIMESTAMP
Current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. LOCALTIMESTAMP
ALTER SESSION SET TIME_ZONE = '-5:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

ALTER SESSION SET TIME_ZONE = '-8:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

- The following statement fails because the mask does not include the TIME ZONE portion of the return type of the function:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

- The following statement uses the correct format mask to match the return type of LOCALTIMESTAMP:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
 
SESSIONTIMEZONE
returns the value of the current session's time zone. SESSIONTIMEZONE
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM dual;

ALTER SESSION SET time_zone = local;
 
SYS_EXTRACT_UTC
Returns Coordinated Universal Time (UTC, formerly Greenwich Mean Time) from a Timestamp SYS_EXTRACT_UTC(<date_time with timezone)
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2004-03-28 11:30:00.00 -08:00')
FROM dual;
 
SYSTIMESTAMP
Current DateTime as a Timestamp SYSTIMESTAMP
SELECT SYSTIMESTAMP FROM dual;
 
TO_TIMESTAMP
Converts a string to an Timestamp Data Type TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)
SELECT TO_TIMESTAMP('2004-12-01 11:00:00','YYYY-MM-DD HH:MI:SS')
FROM dual;
 
TO_TIMESTAMP_TZ
Converts a string to an Timestamp with Timezone Data Type TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)
SELECT TO_TIMESTAMP_TZ('2004-12-01 11:00:00 -8:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM dual;
 
TZ_OFFSET
Returns the TimezoneOffset of a value TZ_OFFSET(<time_zone>)
SELECT TZ_OFFSET('US/Eastern') FROM dual;
 
Timestamp Data Type Demos

Table Without Time Zone

 CREATE TABLE ts_test (
x TIMESTAMP,
y TIMESTAMP(0),
z TIMESTAMP(9));

DESC ts_test

INSERT INTO ts_test
VALUES
(timestamp'2004-08-08 09:00:00.123456789',
timestamp'2004-08-08 09:00:00.123456789',
timestamp'2004-08-08 09:00:00.123456789');

SET LINESIZE 140
COLUMN x FORMAT a30
COLUMN y FORMAT a21
COLUMN z FORMAT a31

SELECT * FROM ts_test;

INSERT INTO ts_test
VALUES
(localtimestamp, localtimestamp, localtimestamp);

SELECT * FROM ts_test;

SELECT VSIZE(x), VSIZE(y), VSIZE(z) FROM ts_test;

COLUMN x CLEAR
COLUMN y CLEAR
COLUMN z CLEAR

Table With Time Zone

 CREATE TABLE tswtz_test (
msg VARCHAR2(40),
x TIMESTAMP WITH TIME ZONE);

DESC tswtz_test

ALTER SESSION SET TIME_ZONE = '-7:00';

COLUMN x FORMAT a35

INSERT INTO tswtz_test
VALUES
('literal TS without TZ', timestamp'2004-08-08 09:00:00.123456789');

INSERT INTO tswtz_test
VALUES
('SysTimeStamp (has TZ from DB)', systimestamp);

INSERT INTO tswtz_test
VALUES
('LocalTimeStamp (has NO TZ)', LocalTimeStamp);

INSERT INTO tswtz_test
VALUES
('Current_Timestamp (has TZ from client)', Current_Timestamp);

SELECT * FROM tswtz_test;

/*
Lastly, notice the behavior of CURRENT_DATE and SYSDATE
Current_Date (new with 9i) is a lot like SYSDATE but it is timezone sensitive!
*/

SELECT TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

ALTER SESSION SET time_zone = local;

SELECT TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROM dual;

COLUMN x CLEAR

Extract Timestamp Components

 SELECT
EXTRACT(year FROM systimestamp),
EXTRACT(month FROM systimestamp),
EXTRACT(day FROM systimestamp),
EXTRACT(hour FROM systimestamp),
EXTRACT(minute FROM systimestamp),
EXTRACT(second FROM systimestamp),
EXTRACT(timezone_hour FROM systimestamp),
EXTRACT(timezone_minute FROM systimestamp),
EXTRACT(timezone_region FROM systimestamp),
EXTRACT(timezone_abbr FROM systimestamp)
FROM dual;

Extract Current Timestamp Components

 SELECT
EXTRACT( year FROM current_timestamp ),
EXTRACT( month FROM current_timestamp ),
EXTRACT( day FROM current_timestamp ),
EXTRACT( hour FROM current_timestamp ),
EXTRACT( minute FROM current_timestamp ),
EXTRACT( second FROM current_timestamp ),
EXTRACT( timezone_hour FROM current_timestamp ),
EXTRACT( timezone_minute FROM current_timestamp ),
EXTRACT( timezone_region FROM current_timestamp ),
EXTRACT( timezone_abbr FROM current_timestamp )
FROM dual;

 

 ALTER SESSION SET time_zone = 'US/Eastern';

SELECT
EXTRACT( year FROM current_timestamp ),
EXTRACT( month FROM current_timestamp ),
EXTRACT( day FROM current_timestamp ),
EXTRACT( hour FROM current_timestamp ),
EXTRACT( minute FROM current_timestamp ),
EXTRACT( second FROM current_timestamp ),
EXTRACT( timezone_hour FROM current_timestamp ),
EXTRACT( timezone_minute FROM current_timestamp ),
EXTRACT( timezone_region FROM current_timestamp ),
EXTRACT( timezone_abbr FROM current_timestamp )
FROM dual;

SET SERVEROUPUT ON

DECLARE
t timestamp WITH time zone := timestamp'1997-01-01 01:01:01 US/Pacific';

BEGIN
   dbms_output.put_line(extract (timezone_abbr from t));
   dbms_output.put_line(extract (timezone_region from t));
END;
/

ALTER SESSION SET time_zone = local;
 
Interval

 

 CREATE TABLE tint_test (
msg VARCHAR2(25),
start_date TIMESTAMP WITH TIME ZONE,
end_date TIMESTAMP WITH TIME ZONE,
duration_1 INTERVAL DAY(5) TO SECOND,
duration_2 INTERVAL YEAR TO MONTH);

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my plane ride',
timestamp'2004-08-08 17:02:32.212 US/Eastern',
timestamp'2004-08-08 19:10:12.235 US/Pacific');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my vacation',
timestamp'2004-07-27 06:00:00',
timestamp'2004-08-04 18:00:00');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my life',
timestamp'1950-01-15 02:00:00',
current_timestamp);

SELECT * FROM tint_test;

UPDATE tint_test
SET duration_1 = (end_date - start_date) DAY(5) TO SECOND,
duration_2 = (end_date - start_date) YEAR TO MONTH;

SELECT msg, duration_1, duration_2 FROM tint_test;

SELECT t.*, end_date - start_date FROM tint_test t;
 
Time Math

 

 SELECT current_timestamp + interval '5' year(1)
FROM dual;

SELECT current_timestamp + interval '10:30' MINUTE TO SECOND
FROM dual;

select timestamp'2004-02-29 00:00:00' + interval '1' year(1)
from dual
/
select timestamp'2004-02-28 00:00:00' + interval '1' year(1)
from dual
/
select add_months( timestamp'2004-02-29 00:00:00',12)
from dual
/

-- you can do math with intervals
SELECT a.duration_1 +b.duration_1+ c.duration_1
FROM tint_test a, tint_test b ,tint_test c
WHERE a.msg = 'my plane ride'
AND b.msg LIKE '%vacat%'
AND c.msg like '%life';

-- but not aggregations.
SELECT SUM(duration_1)
FROM tint_test;
Last Updated ( Wednesday, 26 January 2005 )
 
Next >
[ Back ]
Who's Online

Warning: Invalid argument supplied for foreach() in /home/httpd/vhosts/j2eeworld.com/httpdocs/modules/mod_whosonline.php on line 32
latest topics
+ Free Porn Clips! 91120 FREE PORNO Movies!
+ pac man free video game 56.hi5.com free hostname movie pic p
+ Fuck the sperm
+ * * * FREE PORNO VIDEO * * * - company middlesex title
+ u0bbvr8x7c5ih1gouw86ywl75
+ u0bbvr8x7c5ih1gouw86ywl75
+ zfzigamaoa31tct06m3itz5czd4
+ zfzigamaoa31tct06m3itz5czd4
+ sex in art northern middlesex
+ arourioug
most download
J2Exe (4844)
J2TrayExe (1792)
J2WinService (1598)
HealthXP
HealthXP: Experience the Benefits of Health Innovations