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

Question

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;

Text and examples are contradicting each other in several details (like: do you want to restrict hours in the result to `0 - 21` or not?). I picked *one* interpretation for my answer and added optional commented clauses. — Erwin Brandstetter, Aug 15 '12 at 12:35
@ErwinBrandstetter Yes, I should fix that. I would like to restrict the hours to the interval the user gives as inputs -that is the timestamps given in the calltime between and cal.stamp ..., which in my example is the interval between 22:00:00 of a day and 04:32:21 of another day, all the days within that interval (including the input days) will show the hourly query counts between 22 and 04:32 etc.- parts. — sm90901, Aug 15 '12 at 12:41
That's still ambiguous: `all the days within that interval (including the input days) will show the hourly query counts between 22 and 04:32 etc.- parts.` Try to be clear! Do you actually want to include 00:00 - 04:32 for the first day, too? — Erwin Brandstetter, Aug 15 '12 at 13:24
@ErwinBrandstetter If you mean whether the July 13 2011 00:00 - 04:32 interval should be included, the answer is no. It should start with July 13 2011, 22:00 and go from there, stopping at July 31 2011, 04:32. — sm90901, Aug 15 '12 at 13:30

score 2 · Accepted Answer · edited May 23 '17 at 11:53

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.

score 1 · Answer 2 · answered Aug 15 '12 at 12:24

This clause:

AND calltime::time >= '00:21:09' AND calltime::time <= '21:11:21'

looks suspicious, given the other clauses.

It's not clear why it's needed, and if the literal times where respectively 22:11:43 and 04:06:04 as given as an example that doesn't work, this clause would filter out everything so you'd end up with 0 counts for every hour.

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

2 Answers2