Timestamps comparison in postgres

Question

I have two queries with similar data(one with previous period and other with current). I need to display this in a kendo line chart. How can I do so**

select date_trunc('hour' , "CreateDateTime"::TIMESTAMP) as c, 
       count(1) as d 
from "ROBOT" 
where "CreateDateTime"::TIMESTAMP between '2018-04-01' and '2018-04-22' 
group by date_trunc('hour', "CreateDateTime"::TIMESTAMP) 

UNION ALL

select date_trunc('hour' , "CreateDateTime"::TIMESTAMP) as a, 
count(1) as b 
from "ROBOT" 
where "CreateDateTime"::TIMESTAMP between '2018-05-01' and '2018-05-22' 
group by date_trunc('hour', "CreateDateTime"::TIMESTAMP);

Answer 1

It looks like you want hourly counts for two months side by side. Assuming that is correct, you want something like:

select date_part('hour', "CreateDateTime"::TIMESTAMP) as hour,
       sum(case when "CreateDateTime"::TIMESTAMP between '2018-04-01' and '2018-04-22' then 1 else 0 end) as apr,
       sum(case when "CreateDateTime"::TIMESTAMP between '2018-05-01' and '2018-05-22' then 1 else 0 end) as may
from "ROBOT"
where "CreateDateTime"::TIMESTAMP between '2018-04-01' and '2018-04-22'
   or "CreateDateTime"::TIMESTAMP between '2018-05-01' and '2018-05-22'
group by 1;

The sum(case...) pattern is a good one to use when you want to produce multiple sums from the same data with different criteria.