ClickHouse时间查询

1、近几个小时

SELECT
	tactics_date
FROM
	la_potential_danger
where
	subtractHours(tactics_date, 7) > toDate(now())

2、今天

SELECT
	tactics_date
FROM
	la_potential_danger
where
	toDate(tactics_date) = today()

3、昨天

SELECT
	tactics_date
FROM
	la_potential_danger
where
	toDate(tactics_date) = yesterday()

4、近几天

SELECT
	tactics_date tacticsDate
FROM
	la_potential_danger
where
	subtractDays(tactics_date, 7) > toDate(now())

5、本周

SELECT 
	tactics_name,
	tactics_id,
	toDate(tactics_date) tacticsDate
FROM
	la_potential_danger
where
	tactics_date BETWEEN subtractDays(tactics_date, toDayOfWeek(tactics_date)) and subtractDays(tactics_date, toDayOfWeek(tactics_date) + 7)

6、本月

SELECT
	tactics_date
FROM
	la_potential_danger
where
	toMonth(tactics_date) = toMonth(now())

7、按时间段（自定义）

SELECT 
	tactics_name,
	tactics_id,
	toDate(tactics_date) tacticsDate
FROM
	la_potential_danger
where 
	tactics_date BETWEEN yesterday() and subtractHours(tactics_date, 1)
	

7、按时间段（自定义）（字符时间格式加减天数）

SELECT 
	tactics_name,
	tactics_id,
	toDate(tactics_date) tacticsDate
FROM
	la_potential_danger
where 
	count_Time
    BETWEEN
    DATE_SUB( str_to_date(#{startTime},'YYYY-MM-DD HH24:MI:SS'), INTERVAL 1 DAY )
     AND
    DATE_SUB( str_to_date(#{endTime},'YYYY-MM-DD HH24:MI:SS'), INTERVAL 1 DAY )

8、每天时间段内

select
	*
from
	la_potential_danger
where
	toYYYYMMDDhhmmss(tactics_date)%1000000 BETWEEN '123000' and '160000'

9、按小时分组

SELECT 
	tactics_name,
	tactics_id,
	concat(toString(toHour(tactics_date)), '-', toString(toHour(tactics_date) + 1), '点') tacticsDate,
	count(*) tacticsCount
FROM
	la_potential_danger
GROUP BY
	tactics_name,
	tactics_id,
	toHour(tactics_date)

10、按天分组

SELECT 
	tactics_name,
	tactics_id,
	toDate(tactics_date) tacticsDate,
	count(*) tacticsCount
FROM
	la_potential_danger
GROUP BY
	tactics_name,
	tactics_id,
	toDate(tactics_date)