I have a CTE-based query in which I retrieve hourly intervals between two given timespans. My query works as following:
Getting start and end datetimes (let's say 07-13-2011 00:21:09 and 07-31-2011 21:11:21) get the hourly total query values between the hourly intervals (in here it's from 00 to 21, a total of 21 hours but this is parametric and depends on the hours I give for the inputs) for each day.
This query works well for inputs in which the hour of the first timestamp is smaller than second one -e.g. 03 AM for first timestamp's hour and 07 AM for second timestamp's hour but there is a problem. When I want to retrieve total counts of query for inputs such as 07-13-2011 22:11:43 and 07-25-2011 04:06:04, I'm having problems. I need to retrieve the total counts of queries like the following:
07-13-2011 22:00:00 143 //representing the total amounts of queries 22:11:43 - 22:59:59 interval-
07-13-2011 23:00:00 121 //representing the total amounts of queries in 23:00:00 -23:59:59 interval-
07-14-2011 00:00:00 65 //00:00:00 - 00:59:59 interval
07-14-2011 01:00:00 51 //01:00:00 - 01:59:59 interval...
07-14-2011 04:00:00 22 //query amount for 04:00:00 - 04:06:04 interval
WITH cal AS (
SELECT generate_series('2011-02-02 00:00:00'::timestamp
, '2012-04-01 05:00:00'::timestamp
, '1 hour'::interval) AS stamp
, qqq AS (
SELECT date_trunc('hour', calltime) AS stamp
, count(*) AS zcount
WHERE calltime >= '07-13-2011 22:00:00'
AND calltime <='07-31-2011 04:33:21'
AND calltime::time >= '22:00:00'
AND calltime::time <= '04:33:21'
-- this calltime::time part obviously doesn't work due to common sense and logic
-- edited it to show what I try to mean
AND date_part('hour', calltime) >= 0
AND date_part('hour', calltime) <= 21
GROUP BY date_trunc('hour', calltime)
, COALESCE (qqq.zcount, 0) AS zcount
LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '07-13-2011 22:00:00'
AND cal.stamp<='07-31-2011 04:33:21'
AND date_part('hour', cal.stamp) >= 0
AND date_part('hour', cal.stamp) <= 21
ORDER BY stamp ASC;
Consider this amended version:
WITH param AS ( SELECT '2011-07-13 22:11:43'::timestamp AS start -- supply start / stop once ,'2011-07-25 04:06:04'::timestamp AS stop ) , cal AS ( SELECT generate_series(date_trunc('hour', p.start) ,date_trunc('hour', p.stop + interval '1h') ,interval '1h') AS h FROM param p ) , q AS ( SELECT date_trunc('hour', calltime) AS h ,count(*) AS ct FROM mytable ,param p WHERE calltime >= p.start AND calltime <= p.stop -- uncomment if you actually want to exclude hours 22 & 23 (?) -- AND extract('hour' FROM calltime) BETWEEN 0 AND 21 GROUP BY 1 ) SELECT cal.h, COALESCE(q.ct, 0) AS ct FROM cal LEFT JOIN q USING (h) -- uncomment if you actually want to exclude hours 22 & 23 (?) -- WHERE extract('hour' FROM cal.h) BETWEEN 0 AND 21 ORDER BY 1;
The major change is to generate hours from the actual time-span right away.
Removed a couple of unneeded conditions.
Using ISO 8601 format for timestamps (That works with every locale).
Find more context and links at this related answer. The only difference: over there it's about a running count.