1. Senior Member
Join Date
Mar 2001
Posts
314
After thinking about this puzzle ( on and off for the last 3 days!), here is my best shot at a solution to the puzzle

I think the extra byte that VSIZE reports for SYSDATE is because of an extra byte reserved for the sign. Let me explain,

If you have ever played with dates from the BC era ( which I did not till Jurij raised this question!) you will remember that oracle displays dates from the BC era with a "-" sign before the date, e.g.

select vsize(to_date(to_char(sysdate,'mm/dd/syyyy'),'mm/dd/syyyy')) Siz,
to_char(sysdate,'mm/dd/syyyy') Dt
from dual

will give you the result
Siz --------- Dt
------------ -------------
8 ------------- 10/08/ 2001

Notice the blank space before 2001. Now doing the same to a date in the BC era, e.g. Jan 1, 4000 BC

select vsize(to_date(to_char(to_date('01/01/4000 bc','mm/dd/yyyy bc'),'mm/dd/syyyy'),'mm/dd/syyyy')) Siz,
to_char(to_date('01/01/4000 bc','mm/dd/yyyy bc'),'mm/dd/syyyy') Dt
from dual

will give you the result
Siz --------- Dt
------------ -------------
8 ---------- 01/01/-4000

Notice the "-" ve sign before 4000.

In view of the above, it is my humble opinion that the "extra" byte that VSIZE reports for SYSDATE, for DISPLAY purposes only (if you store SYSDATE into a col and do a VSIZE on that col, you will get 7 bytes - it is only while displaying that VSIZE reports 8 bytes), is because of the sign, which takes up one byte similar to storage for -ve numbers .

How about that for an explanation ?????????

-amar

P.S. Jurij, I did follow your suggestion and clicked on the link And guess what I found? "s" does not stand for scale, but assumes a value of either 0 or 1 depending on whether the number is +ve or -ve NO OFFENCE PLEASE! - amar

2. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by amar
P.S. Jurij, I did follow your suggestion and clicked on the link And guess what I found? "s" does not stand for scale, but assumes a value of either 0 or 1 depending on whether the number is +ve or -ve NO OFFENCE PLEASE! - amar
Gee, why should I be offended? I sincerily hope I didn't left the impression that I am being offended whenever someone doesn't agree with me ! On the contrary, I have (and there have been quite a few such occasions on this forums) allways addmited my mistake when anyone provided *facts* that proved me wrong. And here again is such example - you are correct regarding the meaning of "s" in the formula, I was wrong! Obviously I didn't think twice when I wrote that "s" stands for "scale". It is more than evident that scale realy doesn't play any role in determining how much space is required to store a number in Oracle. I was mislead by the fact that in various formulas describing numbers in Oracle documentation usualy "p" stands for precission and "s" for scale. Well, not in this formula, here "s" stands for sign. Once again, I was wrong!

Now let's go back to the subject of VSIZE() and dates....

.... In view of the above, it is my humble opinion that the "extra" byte that VSIZE reports for SYSDATE, for DISPLAY purposes only (if you store SYSDATE into a col and do a VSIZE on that col, you will get 7 bytes - it is only while displaying that VSIZE reports 8 bytes), is because of the sign, which takes up one byte similar to storage for -ve numbers .
I would say: excelent observations, however your conclusions about the sign as a reason for the extra byte are not correct. Anyway, you spoted the exact reason for the apparent discrepancy between DATE storage format (7 bytes) and VSIZE() on SYDATE or any other function that returns DATE value (8 bytes). Let's make it more understandable by the following test:

SQL> create table test_date (c1 DATE);

Table created.

SQL> insert into test_date values(
2 to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'));

1 row created.

SQL> select vsize(to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'))
2 from dual;

VSIZE(TO_DATE('01-DEC-200112:59:00','DD-MON-YYYYHH24:MI:SS'))
-------------------------------------------------------------
8

SQL> select vsize(c1) from test_date;

VSIZE(C1)
----------
7

So it is obvious that VSIZE() returns 7 for stored dates, while it returns 8 for the dates that are returned from the functions. In other words, DATE datatype has different internal format *inside the database* (where DATE datatype requires 7 bytes) than the internal format of DATE datatype for host variables in programs is (8 bytes)! The difference in this two formats becomes more visible if we use DUMP() function. Let's see what DUMP() returns for the above two examples:

SQL> select dump(to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'))
2 from dual;

DUMP(TO_DATE('01-DEC-200112:59:00'
----------------------------------
Typ=13 Len=8: 209,7,12,1,12,59,0,0

SQL> select dump(c1) from test_date;

DUMP(C1)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,101,12,1,13,60,1

We immediately see that the types are different (type 12 represents internal DATE datatype, while I haven't found any mentioning of type 13 in the documentation). It is also evident, that first case requires 8 bytes, while the second only 7. It is also obvious that even in the first case the date itself is stored in the first 7 bytes and that in both cases first two bytes represent the year, while other 5 bytes represent month, day, hour, minutes and seconds respectively.

The internal representation of each component is different in both cases, though. In first case year is obtained by multiplying the second byte with 256 and adding the first byte (7*256+209=2001), while other date elements are stored in normal decimal notation (for example, 59 minutes is stored as 59 in 6th byte). In the second case, where we deal with how dates are realy stored in the database, the first byte represent centuries and the second one years, both in "exces-100" notation. So the year in our example is (120-100)*100 + (101-100)= 2001. Month and date are stored in normal notation as in the first case, while the hours, minutes and seconds are stored in "excess-1" notation - for example we obtain 59 minutes in our case by subtracting 1 from the value stored in the 6th byte (60-1=59).

And then there is an extra, 8th byte in the first case. Why is it used for? I don't know! So why did I say above that your conclusions about an extra byte as a "sign indicator" are wrong? Because I have played around with various dates, both BC and AD, and that extra 8th byte in the first case allways contains value 0! The "negative sign" (or BC/AD indicatior) is implicitely stored in the first byte. If the first byte contains values between 0 and 18 (if most significant bit is '0') then the date is in AD era, if it is between 237 and 255 (if MSB is '1') the date is in BC era. But in all cases that extra eight byte remains 0. So does anyone have any other idea what that byte is used for?

3. Senior Member
Join Date
Mar 2001
Posts
314
Bhoo woo!! There goes my theory

We don't have anybody from the Oracle Corp as members ??

-amar

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•