CODEWITHSUNDEEP
postgresqldatetimetimestampcommon-table-expression
sm90901
sm90901

Reputation: 255

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve total amount of user requests between two given timestamps and shows how many requests are made in each hour between the two dates. My query works as following:

1)Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 15:11:21)

2)Retrieving the amount of requests in each hour -1 hour intervals that is- for given timestamps. (if the hour of starting timestamp is 10 and hour of ending timestamp is 15 like above, I should display the hourly amount of requests for the hours 10,11,12,13,14,15 for every day between July 13 and 31)

The query is like this:

WITH cal AS (
    SELECT generate_series('2-2-2011 00:00:00'::timestamp , '1-4-2012 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 >= '13-7-2011 10:21:09' 
          AND calltime <= '31-7-2011 15:11:21' AND calltime::time >= '10:00:00' AND
          calltime::time <= '15:59:59' AND date_part('hour', calltime) >= 8 AND 
          date_part('hour', calltime) <= 15
 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 >= '13-7-2011 10:00:00' AND cal.stamp <= '31-7-2011 15:11:21' 
       AND date_part('hour', cal.stamp) >= 10 AND 
       date_part('hour', cal.stamp) <= 15
ORDER BY stamp ASC;

This query shows the amount of requests between hours 10 and 15 are shown in hourly intervals (X requests between 10:21:09 to 11:00, Y requests between 11:00 to 11:59, Z requests between 12:00 to 12:59..., P requests between 15:00 to 15:11:21) for every single day starting on July 13 -10 am to 3 pm- and ending on July 31 -10 am to 3 pm-

It works well for inputs in which the hour value of the first timestamp is smaller than the hour value of the second one -e.g. in here, 10 for first timestamp's hour and 15 for second timestamp's hour- but there is a problem. When I want to apply this to inputs such as 07-13-2011 22:11:43 and 07-31-2011 04:06:04, -so that I get the amount of requests per hour between 22 and 04 for every single day, I'm having problems -due to the structure of my query, most likely-. I need to retrieve the total counts of requests like the following:

    stamp                 zcount
    "2011-07-13 22:00:00"  123
    "2011-07-13 23:00:00"  338
    "2011-07-14 00:00:00"  184
    "2011-07-13 01:00:00"  298
    "2011-07-13 02:00:00"  162
    "2011-07-13 03:00:00"  293
    "2011-07-14 04:00:00"  216
    "2011-07-14 22:00:00"  392
    "2011-07-14 23:00:00"  268
    "2011-07-15 00:00:00"  483
    "2011-07-15 01:00:00"  327
    "2011-07-15 02:00:00"  419
    "2011-07-15 03:00:00"  121
    "2011-07-15 04:00:00"  231
     .
     . 
    "2011-07-30 22:00:00"  392
    "2011-07-30 23:00:00"  268
    "2011-07-31 00:00:00"  483
    "2011-07-31 01:00:00"  327
    "2011-07-31 02:00:00"  419
    "2011-07-31 03:00:00"  544
    "2011-07-31 04:00:00"  231

What do I need to change in my query in order to make sure it works for cases when the hour value of second timestamp is less than the hour value of the first timestamp -e.g 22 for first timestamp and 4 for second timestamp in here-? I currently use a workaround for this but it's not really elegant and I would like to change that.

Upvotes: 1

Views: 2071

Answers (1)

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95682

I'll be honest. I'm not sure I'm even close.

I dropped most of the stuff from the WHERE clause in qqq, because it's repeated in the where clause of the outer query. I also switched to ISO timestamps, because they're infinitely easier for me to read.

You'll probably want to rewrite this as a function you can pass arguments to.

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 >= '2011-07-13 20:21:09' 
    AND calltime <= '2011-07-31 15:11: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 >= '2011-07-13 20:00:00' 
  AND cal.stamp <= '2011-07-31 15:11:21' 
  AND (
    extract ('hour' from cal.stamp) >= extract ('hour' from '2011-07-13 20:00:00'::timestamp) or
    extract ('hour' from cal.stamp) <= extract ('hour' from '2011-07-31 15:11:21'::timestamp) 
  )
ORDER BY stamp ASC;

If there's any magic here, it's probably in this clause. Note the use of "or".

AND (
  extract ('hour' from cal.stamp) >= extract ('hour' from '2011-07-13 20:00:00'::timestamp) or
  extract ('hour' from cal.stamp) <= extract ('hour' from '2011-07-31 15:11:21'::timestamp) 
)

Upvotes: 4

Related Questions