问题
1、presto中的create_time的日期格式是字符型的‘2023-09-28 00:03:33’,我该怎样筛选日期范围?
2、筛选完成后的日期范围,我该怎么自动调整这个范围而不用每次人工输入?
方法
问题1的方法:
方法1: format_datetime(cast(create_time as timestamp),‘yyyyMMdd’)
方法2:date_format(cast(create_time as timestamp),‘%Y%m%d’)
方法3: substr(create_time,1,10)
问题2的方法:
format_datetime(cast(create_time as timestamp),‘yyyyMMdd’) between ‘{@date-10}’ AND ‘{@date-3}’
代码
select
create_time,
cast(create_time as timestamp),
format_datetime(cast(create_time as timestamp),'yyyyMMdd'),
date_format(cast(create_time as timestamp),'%Y%m%d'),
substr(create_time,1,10),
'{@date-10}',
'{@date+100}'
from
default.hw_wenda_tblwendaanswercheck
where
dt='{@date}'
and format_datetime(cast(create_time as timestamp),'yyyyMMdd') between '20230901' AND '20230903'
and date_format(cast(create_time as timestamp),'%Y%m%d') between '20230901' AND '20230903'
and substr(create_time,1,10) between '2023-09-01' and '2023-09-03'
and format_datetime(cast(create_time as timestamp),'yyyyMMdd') between '{@date-10}' AND '{@date-3}'
limit 10