You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Jark Wu <im...@gmail.com> on 2021/03/01 02:38:48 UTC
Re: Best way to handle BIGING to TIMESTAMP conversions
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í <se...@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í
>
Re: Best way to handle BIGING to TIMESTAMP conversions
Posted by Yik San Chan <ev...@gmail.com>.
I think you can also do CAST((e / 1000) AS TIMESTAMP)
On Tue, Mar 2, 2021 at 7:27 PM Sebastián Magrí <se...@gmail.com> wrote:
> Thanks a lot Jark,
>
> On Mon, 1 Mar 2021 at 02:38, Jark Wu <im...@gmail.com> wrote:
>
>> 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í <se...@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í
>>>
>>
>
> --
> Sebastián Ramírez Magrí
>
Re: Best way to handle BIGING to TIMESTAMP conversions
Posted by Sebastián Magrí <se...@gmail.com>.
Thanks a lot Jark,
On Mon, 1 Mar 2021 at 02:38, Jark Wu <im...@gmail.com> wrote:
> 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í <se...@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í
>>
>
--
Sebastián Ramírez Magrí