[Python] DuckDB query that works with time intervals produces incorrect values

Running through python - no tables needed. See below query and result:

import duckdb

sampling_period_sec = 13
date_range = ('2023-01-01', '2023-01-02')

db_conn = duckdb.connect()

db_conn.query(
f"""
DROP TABLE IF EXISTS date_range_query;
CREATE TEMPORARY TABLE date_range_query AS
SELECT
DATE_ADD(
CAST('1970-01-01 00:00:00' AS TIMESTAMP),
TO_SECONDS(CAST(
CEIL(
EXTRACT(EPOCH FROM RANGE)
/ {sampling_period_sec}) AS INT
) * {sampling_period_sec}
)
) AS ts_event
FROM RANGE(
TIMESTAMP '{date_range[0]}',
TIMESTAMP '{date_range[1]}',
INTERVAL {sampling_period_sec} SECOND);

SELECT
t1.ts_event
,t2.ts_event as ts_window
,EXP((MILLISECOND(t2.ts_event - t1.ts_event) / 10**3) / {sampling_period_sec} / {9}) as inter_decay
FROM date_range_query t1
JOIN date_range_query t2
ON t2.ts_event BETWEEN t1.ts_event - INTERVAL
{int(10 * sampling_period_sec)} SECOND AND t1.ts_event
AND t1.ts_event in ('2023-01-01 00:01:03', '2023-01-01 00:01:16')
ORDER BY 1, 2
"""
).df()


Result:


ts_event ts_window inter_decay
0 2023-01-01 00:01:03 2023-01-01 00:00:11 0.641180
1 2023-01-01 00:01:03 2023-01-01 00:00:24 0.716531
2 2023-01-01 00:01:03 2023-01-01 00:00:37 0.800737
3 2023-01-01 00:01:03 2023-01-01 00:00:50 0.894839
4 2023-01-01 00:01:03 2023-01-01 00:01:03 1.000000
5 2023-01-01 00:01:16 2023-01-01 00:00:11 0.958165
6 2023-01-01 00:01:16 2023-01-01 00:00:24 0.641180
7 2023-01-01 00:01:16 2023-01-01 00:00:37 0.716531
8 2023-01-01 00:01:16 2023-01-01 00:00:50 0.800737
9 2023-01-01 00:01:16 2023-01-01 00:01:03 0.894839
10 2023-01-01 00:01:16 2023-01-01 00:01:16 1.000000


The bold-face row, 5, is the problem. The inter_decay should be lower than in row 6, but it is not. Should be 0.624949 but it is 0.958165.

I was able to show that:

SELECT
MICROSECOND(TIMESTAMP '2023-01-01 00:00:24' - TIMESTAMP '2023-01-01 00:01:06'),
MICROSECOND(TIMESTAMP '2023-01-01 00:00:11' - TIMESTAMP '2023-01-01 00:01:06')


Produces: -52000000, -5000000

This explains the problem. But then after playing a little with the calculations (using MICROSECONDS/MILLISECONDS and POSITIVE/NEGATIVE intervals) the values for the interval came out correct. But the main query above still produces a wrong decay. This looks like some problem with DuckDB's engine, because I can't figure out what I am doing incorrectly, if anything.

ADDITION:

Adding another column:

MILLISECOND(t2.ts_event - t1.ts_event) / 10**3 as delta_time


Results - shows my claim:


ts_event ts_window delta_time inter_decay
0 2023-01-01 00:01:03 2023-01-01 00:00:11 -52.0 0.641180
1 2023-01-01 00:01:03 2023-01-01 00:00:24 -39.0 0.716531
2 2023-01-01 00:01:03 2023-01-01 00:00:37 -26.0 0.800737
3 2023-01-01 00:01:03 2023-01-01 00:00:50 -13.0 0.894839
4 2023-01-01 00:01:03 2023-01-01 00:01:03 0.0 1.000000
5 2023-01-01 00:01:16 2023-01-01 00:00:11 -5.0 0.958165
6 2023-01-01 00:01:16 2023-01-01 00:00:24 -52.0 0.641180
7 2023-01-01 00:01:16 2023-01-01 00:00:37 -39.0 0.716531
8 2023-01-01 00:01:16 2023-01-01 00:00:50 -26.0 0.800737
9 2023-01-01 00:01:16 2023-01-01 00:01:03 -13.0 0.894839
10 2023-01-01 00:01:16 2023-01-01 00:01:16 0.0 1.000000

Continue reading...