|
|
Oracle 2013. 4. 9. 09:36
Michael -- Thanks for the question regarding "Timestamp vs. Date - is Date still appropriate for new systems?", version 10.2
Submitted on 26-Nov-2006 2:49 UTC
Last updated 18-Feb-2010 9:54
You AskedThe new Timestamp datatypes are described as extensions of the Date datatype in Oracle's
documentation.
They are clearly more powerful and include capabilities such as tracking times down to
milliseconds, incorporating time zones, etc.
It's also generally accepted that you should minimize the complexity of a system where
possible, and that one way of doing this is not to use redundant entities (in the general
sense, not the ER diagram sense).
Given these points, is there any reason to still use Dates in new system development?
Are there issues with performance, compatibility, etc? Or is this like the switch from
LONG and LONG RAW to LOBs where all new development should use the LOB? Oracle's
decision not to extend Date to also include Date With Timezone and Date With Local
Timezone suggests that Oracle doesn't see the point in extending Date... presumably
because it will be deprecated and replaced with Timestamp.
-----------------------
BTW, on a closely related topic, I don't understand the logic behind making Timestamp
With Time Zone and Timestamp With Local Time Zone datatypes.
Seems to be that a database (or any other system) should store all Date / Times in a
canonical form - presumably seconds since an agreed on starting second. Then all of this
Time Zone stuff is just a matter of Locale and client settings - my client should specify
its Timezone to the Server and then all dates / times should be converted to the
canonical form using that timezone unless I specify another time zone together with the
date / time.
The With Local Timezone and With Timezone datatypes seem to describe how to present the
information to clients more than what the datatype is.
and we said...I think dates will be here for a long time to come.
Timestamp is an ANSI thing, for compliance.
The choice is yours.
And you cannot really store things in a canonical format - there are too many variations.
Dates are 7 bytes always
Timestamps are also 7 (no fractional seconds)
or 11 (fractional seconds)
or 13 (fractional seconds with timezone)
Probably - just using timestamp would be good going forward (there might be tools that
cannot "deal" correctly with a timestamp yet - they would cause it to convert to a date -
you'll use to_timestamp and such with them).
But in reality, date is just coming out of my fingers still :) I typically have no need
for the fractional seconds nor timezone.
 What about variations? November 26, 2006 - 10am UTC Reviewer: Mike Friedman from Shenzhen, China Mind explaining this a bit more?
"And you cannot really store things in a canonical format - there are too many variations."
Does this mean that Oracle doesn't store dates internally as seconds since a reference date / time?
I know Windows does... and they have to deal with as many variations as Oracle, don't they?
Followup November 26, 2006 - 11am UTC: The date datatype does, but not a "seconds since something".
(windows cannot do a wide range of dates? hmmm, you might not be entirely accurate on that seconds
thing, I'll bet windows uses more than one format to store dates, or they cannot store dates that
fall outside a very very very small range!)
<quote src=Expert Oracle Database Architecture>
DATE Type
The DATE type is a fixed-width 7-byte date/time datatype. It will always contain the seven
attributes of the century, the year within the century, the month, the day of the month, the hour,
the minute, and the second. Oracle uses an internal format to represent that information, so it is
not really storing 20, 05, 06, 25, 12, 01, 00 for June 25, 2005, at 12:01:00. Using the built-in
DUMP function, we can see what Oracle really stores:
ops$tkyte@ORA10G> create table t ( x date );
Table created.
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '25-jun-2005 12:01:00',
3 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
The century and year bytes (the 120,105 in the DUMP output) are stored in an excess-100 notation.
You would have to subtract 100 from them to determine the correct century and year. The reason for
the excess-100 notation is support of BC and AD dates. If you subtract 100 from the century byte
and get a negative number, it is a BC date, for example:
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '01-jan-4712bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
So, when we insert 01-JAN-4712BC, the century byte is 53 and 53 100 = 47, the century we
inserted. Because it is negative, we know that it is a BC date. This storage format also allows the
dates to be naturally sortable in a binary sense. Since 4712 BC is less than 4710 BC, wed like a
binary representation that supports that. By dumping those two dates, we can see that 01-JAN-4710BC
is larger than the same day in 4712 BC, so they will sort and compare nicely:
ops$tkyte@ORA10G> insert into t (x) values
2 ( to_date( '01-jan-4710bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
01-JAN-10 Typ=12 Len=7: 53,90,1,1,1,1,1
The month and day bytes, the next two fields, are stored naturally, without any modification. So,
June 25 used a month byte of 6 and a day byte of 25. The hour, minute, and second fields are stored
in excess-1 notation, meaning we must subtract 1 from each component to see what time it really
was. Hence midnight is represented as 1,1,1 in the date field.
This 7-byte format is naturally sortable, as you have seenit is a 7 byte field that can be sorted
in a binary fashion from small to larger (or vice versa) very efficiently. Additionally, its
structure allows for easy truncation, without converting the date into some other format. For
example, truncating the date we just stored, 25-JUN-2005 12:01:00, to the day (remove the hours,
minutes, seconds) is very straightforward. Just set the trailing three bytes to 1,1,1 and the time
component is as good as erased.
</quote>
Timestamps get more complicated - there are timestamps without timezones, with local timezones -
you cannot store those in "one fashion" - one preserves the original timezone for example (so you
cannot put it into UTC, you need to remember what timezone it used to be) and the other puts it
into the database timezone.
 Thanks November 26, 2006 - 12pm UTC Reviewer: Michael Friedman from Shenzhen, China
to_timestamp September 10, 2008 - 8pm UTC
Reviewer: Paras Bansal from Milpitas, CA Can you explain this -
SELECT cast(to_timestamp('01-may-2005 23:59:59.501','dd-mon-yyyy hh24:mi:ss:ff') AS DATE) FROM
dual;
It returns me 2nd may as DATE. Can we do something so that it truncs the millisec portion and we
get only 1st May.
regards,
Paras Bansal
Followup September 16, 2008 - 11am UTC: substr the string you are trying to convert and just use to_date. ops$tkyte%ORA10GR2> SELECT to_date(substr( '01-may-2005 23:59:59.501', 1, 20),'dd-mon-yyyy
hh24:mi:ss') from dual;
TO_DATE(SUBSTR('01-M
--------------------
01-may-2005 23:59:59
 curious TO_TIMESTAMPing around September 16, 2008 - 2pm UTC Reviewer: Duke Ganote from Amelia, Ohio USA SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 16 13:35:17 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the OLAP and Data Mining Scoring Engine options
[glogin.sql includes:
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
]
select cast(ts1 as date) dt1
, ts1
, cast(ts2 as date) dt2
, ts2
from
(
select to_timestamp('01-may-2005 23:59:59.501'
,'dd-mon-yyyy hh24:mi:ss:ff') ts1
, systimestamp ts2
from dual
)
/
DT1
TS1
DT2
TS2
--------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2008-09-16 13:33:39
16-SEP-08 01.33.38.512485 PM -04:00
Followup September 16, 2008 - 11pm UTC: do you have access to support to file a bug?
 where is the bug ? September 17, 2008 - 4am UTC Reviewer: Sokrates sorry, I don't see it.
Can you please explain ?
What was expected output ?
Followup September 17, 2008 - 9am UTC: when you cast a to_timestamp value as a date - the time component disappeared. when you cast a timestamp value as a date - the time component did NOT disappear.
Isn't it just rounding February 16, 2010 - 8pm UTC
Reviewer: Warwick from Sydney, Australia Hi Tom,
Isn't this due to rounding? I tried this using both 9i and 11r1
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
Running the original query returned the results above. Changing it to
select cast(ts1 as date) dt1
, ts1
, cast(ts2 as date) dt2
, ts2
from
(
select to_timestamp('01-may-2005 23:59:58.501'
,'dd-mon-yyyy hh24:mi:ss:ff') ts1
, systimestamp ts2
from dual
)
/
DT1
TS1
DT2
TS2
---------------------
2005-05-01 23:59:59
2005-05-01 23:59:58.501000000
2010-02-17 12:49:57
17-FEB-10 12.49.56.986028 PM +11:00
Cheers
Warwick
Followup February 17, 2010 - 8am UTC: 9i and 10g (and 11gr1) all lose the time component: ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
Session altered.
ops$tkyte%ORA9IR2> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
Session altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select cast(ts1 as date) dt1
2 , ts1
3 , cast(ts2 as date) dt2
4 , ts2
5 from
6 (
7 select to_timestamp('01-may-2005 23:59:59.501'
8 ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
9 , systimestamp ts2
10 from dual
11 )
12 /
DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:54:28
17-FEB-10 09.54.27.579168 AM -05:00
10g: ops$tkyte%ORA10GR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
ops$tkyte%ORA10GR2> select cast(ts1 as date) dt1
2 , ts1
3 , cast(ts2 as date) dt2
4 , ts2
5 from
6 (
7 select to_timestamp('01-may-2005 23:59:59.501'
8 ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
9 , systimestamp ts2
10 from dual
11 )
12 /
DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:55:29
17-FEB-10 09.55.28.592716 AM -05:00
and 11gR1: ops$tkyte%ORA11GR1> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
ops$tkyte%ORA11GR1> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
Session altered.
ops$tkyte%ORA11GR1> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
Session altered.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select cast(ts1 as date) dt1
2 , ts1
3 , cast(ts2 as date) dt2
4 , ts2
5 from
6 (
7 select to_timestamp('01-may-2005 23:59:59.501'
8 ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
9 , systimestamp ts2
10 from dual
11 )
12 /
DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:56:01
17-FEB-10 09.56.01.087210 AM -05:00
but 11gr2: ops$tkyte%ORA11GR2> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
ops$tkyte%ORA11GR2> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
Session altered.
ops$tkyte%ORA11GR2> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
Session altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select cast(ts1 as date) dt1
2 , ts1
3 , cast(ts2 as date) dt2
4 , ts2
5 from
6 (
7 select to_timestamp('01-may-2005 23:59:59.501'
8 ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
9 , systimestamp ts2
10 from dual
11 )
12 /
DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-01 23:59:59
2005-05-01 23:59:59.501000000
2010-02-17 09:57:22
17-FEB-10 09.57.22.406995 AM -05:00
 Isn't it rounding to nearest second? February 18, 2010 - 9am UTC Reviewer: John Abate from Missouri, USA It looks to me that it is rounding the .501 fraction of a second to the nearest whole second
01-may-2005 23:59:59.501 rounds to 02-may-2005 00:00:00
Or am I missing something?
Followup February 18, 2010 - 9am UTC: doh, you are correct, I didn't notice the DATE flipped! and I now do vaguely recall that the behavior changed - in that the timestamp would trunc() not round in 11g. You are correct - I did not see the forest for all of the trees. I was so concentrated on the time going to 00:00:00, that I did not even really look at what the time was beforehand... | | | |
Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77562635789106
Oracle 2013. 3. 6. 15:35
Oracle Club에서 작성된 Oracle PL/SQL 기초 교육 문서입니다.
자세한 내용은 첨부파일의 내용을 확인해보세요.
 pl_sql.doc
Oracle 2013. 1. 21. 17:47
Oracle DB에서 특정 Table의 Data를 Record별로 Text File을 생성하여 내보내고 싶을 때 어떻게 할까?
UTL_FILE 패키지를 이용하여 PL/SQL에서 파일의 입력 or 출력을 할 수 있다.
이는 PL/SQL에서 Log를 File별로 생성하고 싶을 때도 사용이 가능하다.
먼저, Oracle Directory를 생성해주어야 한다.
|
▣ Create Directory
DBA 계정으로 oracle에 로그인을 한 후, Directory를 생성하고, 이를 사용하고자 하는 계정에 부여하면 된다.
1. Create Directory exp_text as 'c:\temp'; --Temp Folder를 Directory로 설정하고, 이를 exp_text로 이름을 부여함
2. grant read, write on directory exp_text to scott; -- Scott 계정에 exp_text Directory에 대한 읽고, 쓰기 권한을 부여함.
|
위와 같이 Directory 생성 및 권한 부여가 되었다면, Scott 계정에서 아래와 같이 간단하게 PL/SQL을 이용하여 Text File로 Data를
추출할 수 있다.
|
▣ Export to Text File
create or replace
procedure Export2Text
IS
vHandle UTL_FILE.FILE_TYPE;
vDirectory varchar2(100);
vFile varchar2(256);
vOutput varchar2(32767);
cursor c_cur is
select empno, ename, hiredate, sal from emp;
begin
vDirectory := 'exp_text';
for r_cur in c_cur
loop
if r_cur.empno > 0 then
vFile := r_cur.empno || '.txt';
vHandle := UTL_FILE.FOPEN(vDirectory ,vFile,'W', 32767);
vOutput := r_cur.ename || ', ' || r_cur.hiredate || ', ' || r_cur.sal;
UTL_FILE.PUT_LINE(vHandle, vOutput);
UTL_FILE.FCLOSE(vHandle);
end if;
end loop;
exception
when others then
UTL_FILE.FCLOSE_ALL;
raise;
end;
/ |
아래는 Directory내에 존재하는 file을 읽어오고자 할 때, 사용할 수 있는 PL/SQL이다.
|
▣ Import to Text File
create or replace
procedure Import2Text
is
vHandle UTL_FILE.FILE_TYPE;
vDirectory varchar2(100);
vFile varchar2(256);
vInput varchar2(32767);
begin
vDirectory := 'exp_text';
vFile := 'test.txt';
vHandle := UTL_FILE.FOPEN(vDirectory ,vFile,'R', 32767);
begin
loop
UTL_FILE.GET_LINE(vHandle, vInput,32767);
DBMS_OUTPUT.PUT_LINE(vInput);
end loop;
exception
when NO_DATA_FOUND then
dbms_output.put_line('last line of file');
end;
UTL_FILE.FCLOSE(vHandle);
exception
when others then
UTL_FILE.FCLOSE_ALL;
raise;
end;
/ |
|
