CODEWITHSUNDEEP
postgresqlcommon-table-expression
sm90901
sm90901

Reputation: 255

Retrieving the start and end hour queries correctly in PostgreSQL Query

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 but there is a problem. It displays hourly amounts but for the start time, it gets all the queries between 00:00:00 and 00:59:59 for each day instead of 00:21:09 - 00:59:59 and same applies for the end time, it gets all the queries between 21:00:00 and 22:00:00 for each day instead of 21:00:00 and 21:11:21. -By the way, the other hour intervals e.g 03:00 - 04:00 etc are currently retrieved normally, no minute and seconds provided, just 1 hour flat intervals- How can I fix that? The query is below, thanks.

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 00:21:09' AND calltime <='07-31-2011 21:11:21' 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 00:00:00' AND cal.stamp<='07-31-2011 21:11:21' AND date_part('hour', cal.stamp) >= 0 AND date_part('hour', cal.stamp) <= 21
ORDER BY stamp ASC;

EDIT:

What I mean with my problem is, despite giving 00:21:09 for my starting hour on first day, the days after that day calculate the total query count for the first hour interval as count of total queries between 00:00:00-01:00:00 instead of 00:21:09-01:00:00.(by the way this should apply to the first hour interval for every day, I can give 04:30:21 for the starting hour and the day will start to count total queries hourly starting from there etc.- Same applies to the ending hour 21:00:00-21:11:21, only the LAST day in the query results take this interval, other days before it take the query count between hour 21 and 22 by counting all queries between 21:00:00-22:00:00 instead of 21:00:00-21:11:21.

For example, if there are 200 queries between 00:00:00 and 01:00:00 on july 14 2011 (the next day after july 13, the start date) but there are 159 queries between 00:21:09 - 01:00:00, I should get 159 queries instead of 200. Also, if there are 300 queries between 21:00:00-22:00:00 on any random day, and 123 of them are between 21:00:00-21:11:21, I should get 123 queries as result instead of 300. (This applies to every single day, other hourly intervals should be counted as usual such as 01:00-02:00, 20:00-21:00 etc. This is parametric, hourly intervals and start-end times depend on user input-

Upvotes: 1

Views: 261

Answers (1)

sm90901
sm90901

Reputation: 255

Adding AND calltime::time >= '00:21:09' AND calltime::time <= '21:11:21' to the WHERE calltime >= '07-13-2011 00:21:09' AND calltime <='07-31-2011 21:11:21' block solved the issue.

Upvotes: 1

Related Questions