Ask about the sql query date filter

When I use local queries with date filtering variables, I expect sql
select date_format(dth, '%Y-%m-%d') as dt,
count(*) as pv,
count(distinct login_ip) uv
from dwd.ad_user_behavior
wheremix_user_behavior.dth BETWEEN date '2024-05-22'
AND date '2024-05-28'
and op_obj_type = 12
and platform = 'scan'
group by time_sec
order by time_sec;

My partition field dth is in the format YYYY-mm-dd HH:mm:ss,
But in fact, the Date dth function causes the sql not to use several partitions, but to traverse the entire table, resulting in poor performance. How does the request solve this problem。

By the way, is there sql query date filter a default date , like pre 7 days

What database are you using? Also why would you use a where clause using a formatted date? You should use the same format you have on the table so it improves performance

Also for relative dates you need to be using field filters: