49-flink sql时间函数实现每周某几天和某些天的指定时间段条件

业务：指定窗口时间，统计每周一到周五，凌晨00点到早上八点的事件条数，超过N次触发输出结果

事件时间格式：yyyy-MM-dd HH:mm:ss.SSS,字段名ts

使用到的函数：DAYOFWEEK(timestamp timestamp)，DATE_FORMAT(date,pattern)   concat（string1,string2,...）

数据源：

drop table aa;
CREATE TABLE IF NOT EXISTS aa (
    `timestamp` VARCHAR,
    `logLevel` VARCHAR, 
    `threadName` VARCHAR,
    `componentId` VARCHAR,    
    ts as TO_TIMESTAMP(`timestamp`,'yyyy-MM-dd HH:mm:ss.SSS')
)
    WITH (  
        'connector' = 'kafka',
        'format' = 'json',
        'properties.bootstrap.servers' = '10.19.120.97:9092,10.19.120.98:9092,10.19.120.99:9092',
        'scan.startup.mode' = 'earliest-offset',
        'topic' = 'logaudit_20220209',
        'properties.group.id' = '02'
        );

执行如下可查看时间字段和格式的结果：

 

select

DAYOFWEEK(timestamp '2022-02-15 16:58:45.000'),

DATE_FORMAT('2022-02-17 10:11:12.100','HH-mm-ss.SSS'),

DATE_FORMAT('2022-02-17 10:11:12.100','HH:mm:ss'),

DATE_FORMAT('2022-02-17 10:11:12.100','HH:mm:ss.SSS'),

concat( DATE_FORMAT( '2022-02-17 10:11:12.100', 'yyyy-MM-dd' ), ' 02:00:00.000' )

查询表aa中ts为周日的记录

DAYOFWEEK函数返回时间戳对应的是周几，注意周日是1，周一是2.......周六是7

select * from aa where DAYOFWEEK(ts) = 1

如何表达当月

where date_format( ts,'%Y-%m' ) = date_format( now(),'%Y-%m' )

每周的周一到周三下午4点和5点的事件，这样写

SELECT ts FROM source_kafka 
where DAYOFWEEK(ts) >1 
and DAYOFWEEK(ts)<5 
and ts > concat( DATE_FORMAT( ts, 'yyyy-MM-dd' ), ' 16:00:00' ) and ts < concat( DATE_FORMAT( ts, 'yyyy-MM-dd' ), ' 17:00:00' )