date conversion problem, calc doesn't like 1967-1972? would like a recheck,

[newbie-02]

hi @all,

i apologize for again having ‘exotic’ problems and silly questions … i had calculation oddities with a start date of 1970-01-01 for a series of timestamps , and got it boiled down to:
‘=24568’ interpreted as a day is 1967-04-06, subsequent integers stand for subsequent days,
‘=1/144’ is an ‘odd’ endless fraction 0,00694444444444444(4), and interpreted as a day fraction equals ~10 minutes (best possible representation in calc),

‘=24568 + 1/144’ formatted as ‘JJJJ-MM-TT" T "HH:MM:SS,000000000000000’ yields
‘1967-04-06 T 00:10:00,000000000000000’, that’s well, and applies for almost all day values from 0 up to today (44300) (disclaimer: i didn’t check em all).

but for a special range, from 24569 to 25568, corresponding to dates 1967-04-07 til 1972-09-26, the sum (say) ‘=24569 + 1/144’ results in e.g. ‘1967-04-07 T 00:10:00,000000099999966’ and the like, all the same fractional string.

i’d say i’m not ‘too bad’ in understanding FP’s and deciphering their oddities, but above is puzzling me. i know it’s a small deviation near the limits of doubles, and wouldn’t be astonished if the difference would come up around a decimal or binary range change?, or would grow grow or shrink in the chain?, but cannot find any source in the values or bits … thus i suspect a weakness in the conversion ‘number to date string’.

could please the one or other reader do a recheck before i file a bug? thanks a lot.

pls. do not! … tell me FP-math is imprecise in general, i know! that but am looking to distinguish between FP- and programming errors so that not too many programming errors remain unprocessed as “it’s just FP, they’re always to blame”

[mikekaganski]

It looks very much related to UNIX epoch (POSIX time) - counting from 1970-01-01. Maybe the results are due to the values around that date are small in some moment of internal processing, and that makes the small deviations not visible in other dates to appear in this range.

Disclaimer: this is just a guess.

[robleyd]

There was leap second added 19720630 - maybe that is somehow related?? Another guess.

[mikekaganski]

See also tdf⁠#127334 comment 8.

[newbie-02]

@mikekaganski: good guess i think,
it’s affecting the range from
1970-01-01 - 1000 to 1970-01-01 + 999, and in the range from
1970-01-01 - 100 to 1970-01-01 + 99 i get different results (less deviation),
imho we can take that as an answer … ???
it’s a point to take into account: date values are different in different ranges (or get different treatment or whatever), when testing always also test in that range …

[sokol92]

Let’s count. There can be a maximum of 16 reliable decimal digits in total; 5 is already in the whole part, therefore, the error may be in the region of 1E-11.

[newbie-02]

@sokol92: it’s not as easy as that, day ‘0’ can’t take 1e-12 into account (conversion), but 2e-12, that works 'til day 2047, 2048 needs 3e-12 to show a reaction, day 16383 (1944) can take 1e-12 into account, 16384 cannot, 25569 (1970-01-01) reacts to 2e-12 and becomes:
1970-01-01 T 00:00:00,000000314321369, the error nagging me is much smaller:
1967-04-07 T 00:10:00,000000099999966, thus likely an ‘fp-artefact’, and visibility depending on conversion routine and value representations,

[sokol92]

We are talking about the original expression “= 24568 + 1/144” and expect an error of the order of magnitude 1E-11. If the error is “significantly” larger, then this may raise questions, if less, then everything is OK.

[newbie-02]

@sokol92:
no - fp-figures don’t store digits but bits, and depending on the range can hold ~15 to ~17 decimal digits, ‘precise to the bit’ in this range would be less than 0,5 times ~3,63E-12 deviation,
no - we are talking why something - most likely a normal fp-artifact - is visible in a special range, and not in others, @mikekaganski already had the - imho - right idea,
no - it’s not ‘everything ok’, it’s good that we learned about that little idiosyncrasy, and better now as we know we have to take it into account, for most users it’s irritating if calculations produce clean results except in some ranges …

[mikekaganski]

@sokol92, @newbie-02: note that the value in the cell (around 25569) cannot have bigger error than immediately before or immediately after. And out of curiosity. try to format the 24568 + 1/144 not ad date+time+decimals, but as date+decimals only (without date part).