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

What exactly do you need to display? What problem do you meet? — Laurenz Albe, May 22 '18 at 07:36

score 1 · Accepted Answer · answered May 22 '18 at 08:37

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.

Timestamps comparison in postgres

1 Answers1