Hi Sebastián, You can use `TO_TIMESTAMP(FROM_UNIXTIME(e))` to get a timestamp value. The BIGINT should be in seconds. Please note to declare the computed column in DDL schema and declare a watermark strategy on this computed field to make the field to be a rowtime attribute. Because streaming over window requires to order by a time attribute.
Best, Jark On Sun, 21 Feb 2021 at 07:32, Sebastián Magrí <sebasma...@gmail.com> wrote: > 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í >
