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;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Retrieving-the-start-and-end-hour-queries-correctly-tp5719698.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
