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

Is there a real quick way to do a query that will show me all the dates <br />given a startdate and an end date?<br
/><br/>Given: 02-01-2006 and 02-28-2006<br />it should give me:<br />02-01-2006<br />02-02-2006<br />...<br />...<br
/>02-27-2006<br />02-28-2006<br /><br />Can this be done by a built-in function perhaps? <br /><br /><br /> 

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-012006-02-022006-02-032006-02-042006-02-052006-02-062006-02-07
(7 rows)

On Fri, Feb 17, 2006 at 04:07:28PM -0500, Henry Ortega wrote:
> Is there a real quick way to do a query that will show me all the dates
> given a startdate and an end date?

You could use generate_series(), which is built-in since 8.0 and
easily written in earlier versions.

SELECT date'2006-02-01' + x
FROM generate_series(0, date'2006-02-28' - date'2006-02-01') AS g(x);

-- 
Michael Fuhr

That usually indicates that, for whatever reason, plpgsql.so is from a different version of PostgreSQL than the
databaseserver.  If you installed PostgreSQL from source, make sure you configured the server to look in the same lib
diras its libs were installed to; if you've installed from package management of some kind (RPM?) make sure you have
thesame versions of all postgres-related packages. 

You should also upgrade, if possible.  7.3 is effectively obsolete (37 releases old); there are a number of bugfixes
andperformance improvements in more recent versions. 

-Owen

-----Original Message-----
From: Henry Ortega [mailto:juandelacruz@gmail.com]
Sent: Friday, February 17, 2006 2:06 PM
To: Owen Jacobson
Subject: Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.


This sounds good. I don't have plpgsql loaded though.

I am trying to load plpgsql and it's giving me:
ERROR:  Load of file /usr/lib/pgsql/plpgsql.so failed: /usr/lib/pgsql/plpgsql.so: undefined symbol: xlateSqlType
createlang: language installation failed

I have pgsql 7.3.2
I am googling and can't seem to find the answer. Any help would be appreciated.


On 2/17/06, Owen Jacobson <ojacobson@osl.com> wrote:
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)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. (supposedly)

I am trying out some really basic function creation such as this:

create function dng2(start_date DATE) returns setof date as $$
declare
aa date:=start_date;

But I always get this
ERROR:  parser: parse error at or near "DATE" at character 33
before I can even finish.

Any idea why this happens?

On Sun, 19 Feb 2006, Henry Ortega wrote:

> I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works.
> (supposedly)
>
> I am trying out some really basic function creation such as this:
>
> create function dng2(start_date DATE) returns setof date as $$
> declare
> aa date:=start_date;

I don't think the beginning is a valid function definition in 7.3.x as I'm
pretty sure it didn't have the grammar support for named parameters.
Also, I think dollar quoting came in 8.0, so that's not going to work
either.  You may be looking at a different version of the docs than the
version you're using.

On Sun, Feb 19, 2006 at 01:47:21PM -0500, Henry Ortega wrote:
> I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works.
> (supposedly)
> 
> I am trying out some really basic function creation such as this:
> 
> create function dng2(start_date DATE) returns setof date as $$
> declare
> aa date:=start_date;
> 
> But I always get this
> ERROR:  parser: parse error at or near "DATE" at character 33
> before I can even finish.

You're using features (named parameters, dollar quotes) that are
available only in 8.0 and later; see the 7.3 documentation for the
correct syntax in that version.  But as someone else mentioned, do
consider upgrading, if not to 8.1.3 or 8.0.7 then at least to 7.3.14.
Lots of bugs have been fixed in the three years since 7.3.2 was
released, some involving data loss.

-- 
Michael Fuhr

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 |            02006-02-13 |            02006-02-14 |            02006-02-15 |
     02006-02-16 |           682006-02-17 |          2562006-02-18 |          1042006-02-19 |           342006-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-012006-02-022006-02-032006-02-042006-02-052006-02-062006-02-072006-02-082006-02-092006-02-102006-02-112006-02-122006-02-132006-02-142006-02-152006-02-162006-02-172006-02-182006-02-192006-02-202006-02-212006-02-222006-02-232006-02-242006-02-252006-02-262006-02-272006-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,