| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Retrieving records in order they were placed.
On Fri, 09 Jul 1999 09:18:41 -0400, Jessica Nocerino <jnocerin_at_nhboston.com>
wrote:
>Using sysdate in a column to timestamp the entrys would also work b/c
>time goes down to ms. it may physically only show date and basic time,
>but time is recorded down to the ms. And when you run a query it uses
>that precision to sort the records
Jessica,
I believe you have been misinformed.
According to the Server Concepts Manual
<quote>
DATE Datatype
The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.
Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
</quote>
There is NO millisecond component of our date datatype.
Consider:
SQL> desc foo
Name Null? Type ------------------------------- -------- ---- D DATE ID NUMBER
SQL> begin
2 for i in 1 .. 1000 loop
3 insert into foo ( id, d ) values ( i, sysdate );
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
1 select distinct to_char( d, 'DD-MON-YYYY HH24:MI:SS' ) d, count(*)
2 from foo
3* group by d
SQL> /
D COUNT(*) -------------------- ---------- 09-JUL-1999 11:38:45 431 09-JUL-1999 11:38:46 569
Since this took over a sec, the have to have different millisecs? Right? Even those with the same sec.
OK, then we should only get a single value, maybe a few that were inserted in the same millisec. Well if we ask for the count of the greatest d we should get something less then 569 records but...
SQL> l
1 select count(*)
2 from foo
3 where d >= all ( select d
4* from foo );
COUNT(*)
569
I hope this clears things up.
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |