Comparison Timestamp vs. Date Data Type

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 Tom's latest followup | Bookmark | Bottom Last updated 18-Feb-2010 9:54 You Asked The 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. Reviews      What about variations?   November 26, 2006 - 10am UTC Bookmark | Bottom | Top 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, we’d 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 seen—it 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 Bookmark | Bottom | Top Reviewer: Michael Friedman from Shenzhen, China  to_timestamp   September 10, 2008 - 8pm UTC Bookmark | Bottom | Top 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 Bookmark | Bottom | Top 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 Bookmark | Bottom | Top 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 Bookmark | Bottom | Top 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 Bookmark | Bottom | Top 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