Re: Given 02-01-2006 to 02-28-2006, output all days.

Henry Ortega wrote:

(question about set of all days between two dates)

I don't know of a builtin way to do it off the top of my head, but it's a pretty simple function to write:

create function days (start date, finish date) returns setof date as $$
declare
curdate date;
begin
curdate := start;
while (curdate <= finish) loop
return next curdate;
curdate := curdate + 1;
end loop;
return;
end;
$$ language plpgsql;

# select * from days ('2006-02-01', '2006-02-07');
days
------------
2006-02-01
2006-02-02
2006-02-03
2006-02-04
2006-02-05
2006-02-06
2006-02-07
(7 rows)

Hello.
I'm having difficulties on my first incursion through generate_series.

The details:

SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
      COUNT (o."04-sms") as totalcause98
      FROM generate_series(11,19) AS s(d)
 LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01'  )::DATE + s.d) and o.cause01=98)
 GROUP BY s.d ORDER BY 1;

This query (although quite messed up on the date parameters), does exactly
what i want:
"sum column 'cause01=98' for a specified date range, including 0's"

date | totalcause98
------------+--------------
2006-02-12 | 0
2006-02-13 | 0
2006-02-14 | 0
2006-02-15 | 0
2006-02-16 | 68
2006-02-17 | 256
2006-02-18 | 104
2006-02-19 | 34
2006-02-20 | 20

I'm using a left join because i really need the =0 sums.
The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
table has a default of 'default (now())::timestamp(2) without time zone'.
So, i can make generate_series work with the left join using the substr.
I was getting ready to optimize this query, when i remembered i also have the
need for another column, 'totalcause99', almost the same as this query, but
with 'cause01=99' as condition.

The maximum i was able to do without syntax errors was:

SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
      COUNT (o."04-sms") as totalcause98,
      COUNT (p."04-sms") as totalcause99
      FROM generate_series(11,19) AS s(d)
 LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01'  )::DATE + s.d) and o.cause01=98)
 LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01'  )::DATE + s.d) and p.cause01=99)
 GROUP BY s.d ORDER BY 1;

Reading this one aloud, i feel the "logic" of what i'm trying to do, but the
values of its output are.. scary to say the least, and the sums are exactly
the same on the 2 columns, and that should never happen with the data i have
on the table.

I'm starting to wonder if this is actually possible to be done on one single
query...
Ideas, anyone?

Sorry for the long email.
Any and all help is deeply appreciated.

Regards,

--
\\pb

Pedro,

Would something such as this suffice?

Mark

create function get_date_range(date, date) returns setof date as '
DECLARE
    cur date;
BEGIN
    cur := $1;

    while cur <= $2 LOOP
         return next cur;
         cur := cur + interval ''1 day'';
    end LOOP;
    return;
END;' language 'plpgsql';

dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
 get_date_range
----------------
 2006-02-01
 2006-02-02
 2006-02-03
 2006-02-04
 2006-02-05
 2006-02-06
 2006-02-07
 2006-02-08
 2006-02-09
 2006-02-10
 2006-02-11
 2006-02-12
 2006-02-13
 2006-02-14
 2006-02-15
 2006-02-16
 2006-02-17
 2006-02-18
 2006-02-19
 2006-02-20
 2006-02-21
 2006-02-22
 2006-02-23
 2006-02-24
 2006-02-25
 2006-02-26
 2006-02-27
 2006-02-28
(28 rows)

On Monday 20 February 2006 15:30, Pedro B. wrote:
> Hello.
> I'm having difficulties on my first incursion through generate_series.
>
> The details:
>
> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
>       COUNT (o."04-sms") as totalcause98
>       FROM generate_series(11,19) AS s(d)
>  LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01'  )::DATE + s.d) andcreate
function get_date_range(date, date) returns setof date as '
DECLARE
cur date;
BEGIN
cur := $1;

while cur <= $2 LOOP
return next cur;
cur := cur + interval ''1 day'';
end LOOP;
return;
END;' language 'plpgsql';

dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
get_date_range
----------------
2006-02-01
2006-02-02
2006-02-03
2006-02-04
2006-02-05
2006-02-06
2006-02-07
2006-02-08
2006-02-09
2006-02-10
2006-02-11
2006-02-12
2006-02-13
2006-02-14
2006-02-15
2006-02-16
2006-02-17
2006-02-18
2006-02-19
2006-02-20
2006-02-21
2006-02-22
2006-02-23
2006-02-24
2006-02-25
2006-02-26
2006-02-27
2006-02-28
(28 rows)
> o.cause01=98)
>  GROUP BY s.d ORDER BY 1;
>
>
> This query (although quite messed up on the date parameters), does exactly
> what i want:
> "sum column 'cause01=98' for a specified date range, including 0's"
>
> date | totalcause98
> ------------+--------------
> 2006-02-12 | 0
> 2006-02-13 | 0
> 2006-02-14 | 0
> 2006-02-15 | 0
> 2006-02-16 | 68
> 2006-02-17 | 256
> 2006-02-18 | 104
> 2006-02-19 | 34
> 2006-02-20 | 20
>
> I'm using a left join because i really need the =0 sums.
> The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
> table has a default of 'default (now())::timestamp(2) without time zone'.
> So, i can make generate_series work with the left join using the substr.
> I was getting ready to optimize this query, when i remembered i also have
> the need for another column, 'totalcause99', almost the same as this query,
> but with 'cause01=99' as condition.
>
> The maximum i was able to do without syntax errors was:
>
> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
>       COUNT (o."04-sms") as totalcause98,
>       COUNT (p."04-sms") as totalcause99
>       FROM generate_series(11,19) AS s(d)
>  LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01'  )::DATE + s.d) and
> o.cause01=98)
>  LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01'  )::DATE + s.d) and
> p.cause01=99)
>  GROUP BY s.d ORDER BY 1;
>
> Reading this one aloud, i feel the "logic" of what i'm trying to do, but
> the values of its output are.. scary to say the least, and the sums are
> exactly the same on the 2 columns, and that should never happen with the
> data i have on the table.
>
> I'm starting to wonder if this is actually possible to be done on one
> single query...
> Ideas, anyone?
>
> Sorry for the long email.
> Any and all help is deeply appreciated.
>
> Regards,