I have a table with two BIGINT fields for start and end of an event as UNIX
time in milliseconds. I want to be able to have a resulting column with the
delta in milliseconds and group by that difference. Also, I want to be able
to have aggregations with window functions based upon the `end` field.
The table definition looks like this:
|CREATE TABLE sessions (
| `ats` STRING,
| `e` BIGINT,
| `s` BIGINT,
| `proc_time` AS PROCTIME(),
| PRIMARY KEY (`ats`, `s`, `e`) NOT ENFORCED
|)
Then I have a few views like this:
CREATE VIEW second_sessions AS
SELECT * FROM sessions
WHERE `e` - `s` = 1000
And some windows using these views like this:
WINDOW w3m AS (
PARTITION BY `t`
ORDER BY `proc_time`
RANGE BETWEEN INTERVAL '3' MINUTE PRECEDING AND CURRENT ROW
)
I'd like to use the `e` field for windowing instead of `proc_time`. But I
keep running into errors with the `TO_TIMESTAMP(BIGINT)` function now
missing or with unsupported timestamp arithmetics.
What is the best practice for a case such as this?
Best Regards,
--
Sebastián Ramírez Magrí
