오라클 TIMESTAMP DATE

출처: 오라클 TIMESTAMP DATE

[출처] 오라클 TIMESTAMP DATE|작성자 마루아라

 

1. TESTTABLE1
CREATE TABLE TESTTABLE1 (
ID NUMBER,
DD TIMESTAMP
)

2. TESTTABLE2
CREATE TABLE TESTTABLE2 (
ID NUMBER,
DD TIMESTAMP WITH LOCAL TIME ZONE
)

 

 TIMESTAMP 사용시 JDK 버전에 따라 -9시간 되어 저장되므로 TIMESTAMP WITH LOCAL TIME ZONE 를 사용하여 테이블 생성하자

테스트 안해봄 ㅡㅡ^

 

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS FF6') 
,TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6')

,TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF')
FROM DUAL

 

SQL> create table conference_calls(

2       title         varchar2(100),

3       phone     varchar2(20),

4       place      varchar2(100),

5       starts      timestamp with time zone )

6       /

    
ps.setTimestamp(index++, ts);

SQL> insert into conference_calls

2        values('Sales Strategy','212.123.4567','Washington',

3                   TIMESTAMP '2006-12-30 15:00:00.000000 EST')

4        /

 

SQL> insert into conference_calls

2       values('Product Features','650.123.4567','San francisco',

3                  TIMESTAMP '2006-12-30 17:00:00.000000 PST')

4       /

 

SQL> insert into conference_calls

2        values('Football highlights','44 1234 5678', 'London',

3                   TIMESTAMP '2006-12-30 20:00:00.000000 GMT')

4       /

 

SQL> select dbtimezone from dual;

 

SQL> select title, phone

2          from conference_calls

3        where starts = TIMESTAMP '2001-12-30' 15:00:00.000000 -5:00'

4        /

 

 

==  오라클의 sysdate 사용하지 않고 date 타입에 현재 날짜시간 넣기  ============================================

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String d = sdf.format(new java.util.Date());
Timestamp ts = new Timestamp(0).valueOf(d);

 

 

==  Timestamp Test  ==========================================

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String d = sdf.format(new Date());
long time = new Date().getTime();
Timestamp ts = new Timestamp(time);
out.print(d);
out.print("<br/>");
out.print(ts);

out.print("<br/>");
out.print("<br/>");

GregorianCalendar gc = new GregorianCalendar();
long tempDate = gc.getTimeInMillis();
Timestamp date = new Timestamp(tempDate);
out.print(tempDate);
out.print("<br/>");
out.print(date);

 

* 결과 *

2009-01-09 09:51:20
2009-01-09 09:51:20.703

1231462280703
2009-01-09 09:51:20.703

 

 

 

==  String 을 Timestamp 로 변환  ==========================================

String 은 "yyyy-mm-dd hh:mm:ss.ffffff" 형식

 

String str = "2006-06-16 11:22:33.123456"
Timestamp st = new Timestamp(0);

 st = st.valueOf (str);

out.print(st);

 

 

==  java.util.Date 를 java.sql.Date 형식으로 변환  ==============================

pstmt.setDate(4, new java.sql.Date(A_Dtime_Date[i][0].getTime()) );
pstmt.setDate(5, new java.sql.Date(A_Dtime_Date[i][1].getTime()) );

 

 

==  java.util.Date 를 java.sql.Timestamp 형식으로 변환  ==============================

pstmt.setTimestamp(4, new java.sql.Timestamp(A_Dtime_Date[i][0].getTime()) );
pstmt.setTimestamp(5, new java.sql.Timestamp(A_Dtime_Date[i][1].getTime()) );

 

 

==  SimpleDateFormat 형식으로 Timestamp 변환  ==============================

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
java.util.Date date = sdf.parse("2004-07-24 09:45:52.189");
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
out.print(timestamp);

 

[출처] 오라클 TIMESTAMP DATE|작성자 마루아라