Elapsed time approximation error?

[steax]

Hi Everyone!

I'm running into a "weird issue", when I calculated elapsed times between date times / timestamp values seems that I run into an approximation error.

I've prepared the following sample script to demonstrate what I mean, hoping that some one could indicate what I'm doing wrong here.

The sample script is supposed to load 10000 records with the same start and end date and time, and then calculate the total elapsed time in seconds among all the records.

The start is always: 2023-09-01 00:00:00, end is always: 2023-09-01 00:00:05 (+5 seconds from start)

The total elapsed time should be 50000 seconds (10000 records * 5 seconds each), instead I get 49999:

Sample script:

Set i = 0;
Do while i < 10000
    TimeStamps:
    Load Timestamp#('2023-09-01 00:00:00', 'YYYY-MM-DD hh:mm:ss') As EventStart
    	, Timestamp#('2023-09-01 00:00:05', 'YYYY-MM-DD hh:mm:ss') As EventEnd
	AutoGenerate 1;    
    i = i + 1;
Loop

Elapsed:
Load Sum(Interval(EventEnd - EventStart, 'ss')) As ElapsedSeconds
Resident TimeStamps;

Thanks for your time

[marcus_sommer]

I assume it's related to the used binary number-system of Qlik which could lead to Rounding Errors - Qlik Community - 1468808.

Beside the question why you want to create n equally records the above tasks doesn't need an outside-loop else might be just:

TimeStamps:
    Load Timestamp#('2023-09-01 00:00:00', 'YYYY-MM-DD hh:mm:ss') As EventStart
    	, Timestamp#('2023-09-01 00:00:05', 'YYYY-MM-DD hh:mm:ss') As EventEnd
	AutoGenerate 10000; 

[hic · Accepted solution]

As Markus points out, it's a rounding error.

2023-09-01 00:00:00 = 45170.000000
2023-09-01 00:00:05 = 45170.000058

So we are talking about a difference between the two numbers in the 10th significant digit, when the matissa just has 15 digits. Adding 10000 numbers will make the rounding (in the 15th digit) visible.

If you instead calculate
Interval(Sum(EventEnd) - Sum(EventStart), 'ss') As ElapsedSeconds
you will get 50004, which shows how large the rounding error can get.

See also on https://community.qlik.com/t5/Design/Rounding-Errors/ba-p/1468808

HIC

[steax]

Thanks both, the article was indeed interesting. The rounding error I can observe in one of my analysis (digesting millions of events) is negligible from and end user prospective, I just wanted to go at the bottom of it to understand what was causing it.

Again thanks for your explanation and have a nice day!