4

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:

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

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)
;

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

yjhmelody
  • 119
  • 1
  • 2
  • 4

1 Answers1

4

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)

This filters the taq table within specified date and time.

PMYoung
  • 41
  • 1