|
|
Oracle 2013. 4. 9. 09:50
A Comparison of Oracle's DATE and TIMESTAMP Datatypes
By James Koopmann
If you want to store date and time information in Oracle, you really only have two different options for the column's datatype. Lets take a quick look at these two datatypes and what they offer.
DATE datatype
This is the datatype that we are all too familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. 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 later in this article when we discuss the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date as in Listing A.
LISTING A:
Formatting a date
SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;
Date
---------------------------
06/20/2003 16:55:14
06/26/2003 11:16:36
About the only trouble I have seen people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned. Check out Listing B for my solution on how to extract the individual time intervals for a subtraction of two dates. I am aware that the fractions could be reduced but I wanted to show all the numbers to emphasize the calculation.
LISTING B:
Determine the interval breakdown between two dates for a DATE datatype
1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
2 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
3 trunc(86400*(date2-date1))-
4 60*(trunc((86400*(date2-date1))/60)) seconds,
5 trunc((86400*(date2-date1))/60)-
6 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
7 trunc(((86400*(date2-date1))/60)/60)-
8 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
9 trunc((((86400*(date2-date1))/60)/60)/24) days,
10 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
11* FROM date_table
DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS
----------------- ----------------- ---------- ---------- ---------- ---------- ----------
06202003:16:55:14 07082003:11:22:57 43 27 18 17 2
06262003:11:16:36 07082003:11:22:57 21 6 0 12 1
TIMESTAMP datatype
One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function as I do in Listing C. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a straight INSERTSELECT FROM and Oracle will do the conversion for you. Look at Listing D for a formatting of the new TIMESTAMP datatype where everything is the same as formatting the DATE datatype as we did in Listing A. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at Listing E. In Listing E, we are only showing 3 place holders for the fractional seconds.
LISTING C:
Convert DATE datatype to TIMESTAMP datatype
SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Date
-----------------------------------------------------
20-JUN-03 04.55.14.000000 PM
26-JUN-03 11.16.36.000000 AM
Formatting of the TIMESTAMP datatype
1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table
Date
-------------------
06/20/2003 16:55:14
06/26/2003 11:16:36
LISTING E:
Formatting of the TIMESTAMP datatype with fractional seconds
1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
Date
-----------------------
06/20/2003 16:55:14:000
06/26/2003 11:16:36:000
Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE
datatype. Look at what happens when you just do straight subtraction of the columns in Listing F. As you can see, the results are much easier to recognize, 17days, 18hours, 27minutes, and 43seconds for the first row of output. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen in Listing G.
LISTING F:
Straight subtraction of two TIMESTAMP datatypes
1 SELECT time1, time2, (time2-time1)
2* FROM date_table
TIME1 TIME2 (TIME2-TIME1)
------------------------------ ---------------------------- ----------------------
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 +000000017 18:27:43.000000
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 +000000012 00:06:21.000000
LISTING G:
Determine the interval breakdown between two dates for a TIMESTAMP datatype
1 SELECT time1,
2 time2,
3 substr((time2-time1),instr((time2-time1),' ')+7,2) seconds,
4 substr((time2-time1),instr((time2-time1),' ')+4,2) minutes,
5 substr((time2-time1),instr((time2-time1),' ')+1,2) hours,
6 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))) days,
7 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
8* FROM date_table
TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS
------------------------- -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
System Date and Time
In order to get the system date and time returned in a DATE datatype, you can use the SYSDATE function such as :
SQL> SELECT SYSDATE FROM DUAL;
In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:
SQL> SELECT SYSTIMESTAMP FROM DUAL;
You can set the initialization parameter FIXED_DATE to return a constant value for what is returned from the SYSDATE function. This is a great tool for testing date and time sensitive code. Just beware that this parameter has no effect on the SYSTIMESTAMP function. This can be seen in Listing H.
LISTING H:
Setting FIXED_DATE and effects on SYSDATE and SYSTIMESTAMP
SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11.05.02.519000 AM -06:00
When working with date and time, the options are clear. You have at your disposal the DATE and TIMESTAMP datatypes. Just be aware, while there are similarities, there are also differences that could create havoc if you try to convert to the more powerful TIMESTAMP datatype. Each of the two has strengths in simplicity and granularity. Choose wisely.
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
|
