sm90901 sm90901 - 29 days ago 8
SQL Question

Query fails when start-time of timestamp is bigger than end-time of timestamp

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


and so on. What do I need to do in addition to the CTE query I have written below?

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
FROM mytable
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)
)
SELECT cal.stamp
, COALESCE (qqq.zcount, 0) AS zcount
FROM cal
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;

Answer

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.