Re: Problems storing in a INTERVAL DAY(9) TO DAY field

On Mar 26, 9:17 am, Gerardo Santana <gerardo.sant...@xxxxxxxxx> wrote:
On 26 mar, 08:12, Jonathan Leffler <jonathan.leff...@xxxxxxxxx> wrote:



On Mar 25, 11:03 am, Gerardo Santana <gerardo.sant...@xxxxxxxxx>
wrote:

Art S. Kagel (Oninit) ha escrito:

Gerardo Santana wrote:
Try the following:

create table test(id serial, dd INTERVAL DAY(9) TO DAY);
insert into test values(0, INTERVAL(99) DAY TO DAY);
insert into test values(0, INTERVAL(100) DAY TO DAY); -- this one
gives a syntax error

why can't I store a 3-digit amount of days?, shouldn't I be able to
store a 9-digit amount of days at most?

You can store 9 digit days, but your insert statement can only handle 2
digits. Try:

insert into test values(0, INTERVAL(100) DAY(9) TO DAY);

Thank you Art.

I have another problem with INTERVALs.

I'm programming in ESQL/C and I really need to interpret the
underlying in_dec (dec_t) data that is part of the intrvl_t structure.

In fact, I already did it. I can succesfully interpret YEAR TO MONTH
and DAY TO SECOND values. But I found an edge case that doesn't follow
the manual, as I understood it.

The dec_t structure contains four fields: dec_exp, dec_pos, dec_ndgts
and dec_dgts. dec_pos gives me the sign, dec_exp gives me number of
digit pairs to count from the left to position the decimal point,
dec_ndgts is the number of significant digit pairs and dec_dgts are
the actual digits.

After retrieving a INTERVAL(3700) YEAR(9) TO YEAR value, I invextend()
it to YEAR TO MONTH (for my own purposes)

invextend(data, &invl);

I do it like this:

intrvl_t *data, invl;

data = (intrvl_t *)var->sqldata;
if (TU_START(data->in_qual) <= TU_MONTH)
invl.in_qual = TU_IENCODE(9, TU_YEAR, TU_MONTH);
else
invl.in_qual = TU_IENCODE(9, TU_DAY, TU_F5);

invextend(data, &invl);

When interpreting the resulting invl value, I find that exp is 7,
ndgts is 1 and dgts[0] is 37, but I expected ndgts to be 2.

My routine is interpreting then 3700 days incorrectly as 37 days,
because it only gets one pair of digits (ndgts == 1) from dgts.

What am I missing?

For now, I have worked around it memset()'ing invl to zero before
ivextend()'ing and, since I'm always extending to YEAR_TO_MONTH, I can
safely ignore dec_ndgts and interpret dec_dgts as if it always holds
months. In my example above, I would access dec_dgts[1] even though
dec_ndgts is 1, because I'm sure ivextend() worked and have preset
invl to zero.

Thanks in advance.

All intervals - both in the YEAR-MONTH and the DAY-FRACTION classes
are stored as if there were seconds lurking around. For example, an
INTERVAL YEAR(9) TO MONTH is stored as if the DECIMAL were:

YYYYYYYYY-MM-00 00:00:00.00000

INTERVAL MONTH(9) TO MONTH: MMMMMMMMM-00 00:00:00.00000
INTERVAL DAY(9) TO SECOND: DDDDDDDDD hh:mm:ss.00000
INTERVAL HOUR(9) TO SECOND: hhhhhhhhh:mm:ss.00000

...you can probably guess the story for minutes and se

And so on. Of course, the fractional digits I cited are not really
stored, but the decimal point is placed appropriately.

Dig around the IIUG web site - there's code in SQLCMD and there should
be some separate ESQL/C code ivconv.ec to mess around with intervals.

Beware: INTERVAL SECOND(9) TO SECOND can only represent up to about 30
years worth of delta - to cover the entire time span from 0001-01-01
to 9999-12-31, it would need to be an INTERVAL SECOND(12) TO SECOND
(and to measure in minutes, we'd need INTERVAL MINUTE(10) TO SECOND).
Conversely, INTERVAL YEAR(9) TO YEAR can cover geological (but not
cosmological) time periods. The code in ivconv.ec takes this factor
into account.

They're fun - they're confusing - they're DATETIMEs and INTERVALs :-D

-=JL=-

:-) Jonathan, thank you for taking the time to respond.

According to thishttp://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.e...

dec_ndgts means the number of _significant_ pairs of digits. But...

an INTERVAL(37) YEAR TO YEAR is stored like this:
ndgts: 1
dgts: 37 26 76 96 00 00 00 06 00

One base-100 digit is significant - the 37; the rest are ignored (I'd
prefer that they were zeroed, but that's life).

an INTERVAL(3700) YEAR TO YEAR is stored like this:
ndgts: 1
dgts: 37 00 11 96 00 00 00 06

Of course; there is one significant centisimal (base-100) digit, the
37. The exponent is one large than for just 37 years. If you had
experimented with 370 years, you'd find ndgits = 2, and the values
would be 03 and 70 and the exponent would be the same as for 3700
years.

I've discussed decimal representations before in this news group - and
there's also code in SQLCMD for dumping decimals and intervals
(dumpdec.ec, dumpintvl.ec) and then there's the ivconv.ec code I
mentioned too.

There's a difference is in the dec_exp part of course, that's what I'm
using to work around this problem by the way.

But my point is that I expected that the value of ndgts were 2 instead
of 1 in the case of INTERVAL(3700) YEAR TO YEAR. I understood that I
was supposed to extract ndgts entries from dgts.

ndgts counts centesimal digits, not decimal digits. The type is
arguably a misnomer (interestingly, that's the word of the day for one
of the online web sites that does that). However, although you
specify numbers of decimal digits, the storage uses centesimal digits,
so there's a factor of two difference, give or take the odd one.

Since ndgts is not 2, I get 37 years in both cases.

The workaround is ignoring ndgts and take as much digits as I need,
according to the class of the INTERVAL of course (I'm extending all
INTERVALs to either YEAR(9) TO MONTH or DAY(9) TO SECOND), with the
help of dec_exp.

That's bad; you'll get junk if you do that. You have to treat the
value correctly. For some work I've been doing, I concluded that I
needed to unpack the decimal part of an interval into a fixed format
structure (actually, an array of unsigned bytes) where there was
enough space for any interval (or datetime) value. The implied
decimal point was in a fixed location. The only tricky bit is that if
an interval is DAY TO SECOND, then certain bytes contain day values,
but if the interval is HOUR TO SECOND, then some of the 'day' storage
locations contain extra digits of the hour part (for HOUR(4) TO
SECOND, the most significant pair of decimal digits, or single most
significant base-100 digit, occupy the space that would be taken by
the least significant digits of an INTERVAL DAY TO SECOND). But I
found that easier to manipulate than the floating decimal structure.

Then the problem is that after extending the INTERVAL, as you can see
in the first dump, there's "garbage" in the rest of the digits. So I
have to memset first to get this:

ndgts: 1, dgts: 37 00 00 00 00 00 00 00

Then I can extract either 37 or 37 00, depending on dec_exp. No
problem any more.

But...

Shouldn't this be documented?, or, where am I wrong in my
suppositions?

Debatable whether it 'should' be documented. Nominally, you use the
ESQL/C functions to handle the type. However, they are not as
complete as you'd like, so you have to delve into the details like
this.

I'm using the following documentation:

....dropped...

Version 10 - OK; the details haven't changed.

By the way, I've reported a pair of errors in the documentation but
I'm not sure if it has reached someone because I haven't got any
answer.

The docinf@xxxxxxxxxx mail alias has had problems recently -
apparently.
Send the messages to me, please, and I'll forward them to Tech Pubs.

HTH.

-=JL=-
.

Relevant Pages

  • Re: Time Accuracy
    ... That means the 864,000 tenths of a second in one day can theoretically be handled by the available decimals. ... Perhaps I should have said unreliable digits rather than unreliable numbers. ... oddities of IEEE floating point representation. ... raised issues about the resolution of the Excel NOWfunction. ...
    (microsoft.public.excel.misc)
  • Re: Math issue
    ... <it is common to almost all computer software and hardware> ... There are implementations of the COBOL and other languages that do not suffer from this problem, because they use either scaled integer representations internally or true binary representations of numbers. ... We should be careful with descriptions like "digits beyond the 15th". ... If I re-format to number and expand the decimals ...
    (microsoft.public.excel)
  • Re: Randomness of digits within pi
    ... had 50 digits). ... Up to 800 million decimals after the point, I got a distribution of: ... output uniform distribution of output you should look for anomalies of ...
    (sci.math)
  • Re: a Fortran precision question
    ... in Fortran will be 0.1234568 //7 digits after decimal point ... approximately twice as much storage space to 'a' as single precision, ... It so happens that, when you convert back to decimals, single precision ...
    (comp.lang.fortran)
  • Re: INFINITY Revisited
    ... >>> approximation to pi with k digits equals pi. ... of elements of each sequence (before and after n in Column A and before ... valid representation of this sequence having decimals each having one ... Just as the number of naturals in the sequence of naturals ...
    (sci.math)