I am trying tunble and hop windowing with SQL table API, i want specific where condition for my log whether to consider in window or not let say i have window-1 for 2:00 - 2:10 where i only want logs of versionNum > 10

SELECT window_start, window_end, COUNT(versionNum)\n" +
 FROM TABLE(
 TUMBLE(TABLE Log, DESCRIPTOR(time_ltz), INTERVAL '2' SECOND))
 GROUP BY window_start, window_end
WHERE versionNum > 10

What’s the issue you’re running into?

the issue was solved by replacing position of group by and where clause, but now @Martijn Visser i want to compare window_start time by performing aggregation with my event time

timestamp

but i am getting this error: might it is possible because of window_start datatype so can you please help

SELECT window_start, window_end, COUNT(*)
FROM TABLE(
TUMBLE(TABLE Log, DESCRIPTOR(time_ltz), INTERVAL '5' SECOND))
WHERE TO_TIMESTAMP_LTZ(`timestamp`,3) < (window_start + INTERVAL '5' SECOND)
GROUP BY window_start, window_end

Exception in thread "main" org.example.exceptions.SQLParseException: Error in executing SQL query
	at org.example.RuleEvaluator.main(RuleEvaluator.java:186)
Caused by: org.apache.flink.table.planner.codegen.CodeGenException: Incomparable types: TIMESTAMP_LTZ(3) and TIMESTAMP(3) NOT NULL
	at org.apache.flink.table.planner.codegen.calls.ScalarOperatorGens$.generateComparison(ScalarOperatorGens.scala:606)
	at org.apache.flink.table.planner.codegen.ExprCodeGenerator.generateCallExpression(ExprCodeGenerator.scala:627)
	at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:490)
	at org.apache.flink.table.planner.codegen.ExprCodeGenerator.visitCall(ExprCodeGenerator.scala:57)
	at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)

The error already says it: your timestamps are not the same type, once is with a local time zone, the other one isn’t

I see, I am trying to aggregate using window and using processing time defined using Schema, the issue here is that none of the events are aggregated by the query now when using event time the window is not terminating, it seams like it is waiting for the event before the result e.g. 1st window started at 1:10 - 1:20 and if after 1:20 there is no events window1 is not producing a result

Schema tmpSchema = Schema.newBuilder()
                .fromResolvedSchema(table.getResolvedSchema())
                .columnByExpression("time_ltz", "PROCTIME()")
                .build();

SELECT window_start, window_end, COUNT(*)
FROM TABLE(
TUMBLE(TABLE Log, DESCRIPTOR(time_ltz), INTERVAL '5' SECOND))
GROUP BY window_start, window_end

If there's no new event coming in that's pushing the watermark forward so that Flink knows that time has progressed, it can't close the window

is there any way to manually close the window at 1:20?

You either change your watermark configuration in case you have an idle source, or you manually push an event that contains an event time that advances the watermark

for the work around i have tried with processing time which does'nt work with the bellow query, can you help what am i doing wrong

SELECT window_start, window_end, COUNT(*), ADD(message.ecs.versionNum, window_start)
   FROM TABLE(
   HOP(TABLE Log, DESCRIPTOR(time_ltz), INTERVAL '1' SECOND, INTERVAL '5' SECOND))
GROUP BY window_start, window_end