如何在Clickhouse中根据日期和时间段选择数据 [英] HOW to SELECT data basing on both a period of date and a period of time in clickhouse

问题描述

我想同时按 yyyymmdd (日期)和 hhmmss (时间)过滤某些数据，但Clickhouse不支持 time 类型.因此，我选择 datetime 进行组合.但是如何做这样的事情:

I want to filter some data by both yyyymmdd(date) and hhmmss(time), but clickhouse don't support time type. So I choose datetime to combine them. But how to do such things:

这是 dolphindb 的代码(它支持 second 类型来表示 hhmmss .

This is code of dolphindb(which supports second type to represent hhmmss.

select avg(ofr + bid) / 2.0 as avg_price
from taq
where
    date between 2007.08.05 : 2007.08.07,
    time between 09:30:00 : 16:00:00
group by symbol, date

这是 clickhouse 的代码，但是是逻辑上有问题的代码.

This is code of clickhouse, but a logical problematic code.

SELECT avg(ofr + bid) / 2.0 AS avg_price
FROM taq
WHERE
    time BETWEEN '2007-08-05 09:30:00' AND '2007-08-07 16:00:00'
GROUP BY symbol, toYYYYMMDD(time)
;

如何像 dolphindb 代码一样在sql中表达它?

how to express it in sql just like the dolphindb code?

推荐答案

假设您只想在正常交易时间内平均交易价格(不包括非盘后交易)，那么可以采取以下解决方案:

Assume that you just want to average the trading price in normal trading hours, excluding after hour trading, then a possible solution:

SELECT avg(ofr + bid) / 2.0 AS avg_price
FROM taq
WHERE
    toYYYYMMDD(time) BETWEEN 20070805 AND 20070807 AND
    toYYYYMMDDhhmmss(time)%1000000 BETWEEN 93000 and 160000
GROUP BY symbol, toYYYYMMDD(time)

这会在指定的日期和时间内过滤 taq 表.

This filters the taq table within specified date and time.

这篇关于如何在Clickhouse中根据日期和时间段选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！