pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGiven 02-01-2006 to 02-28-2006, output all days.
9+ messages / 6 participants
[nested] [flat]
* Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-17 21:07 Henry Ortega <juandelacruz@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Henry Ortega @ 2006-02-17 21:07 UTC (permalink / raw)
To: pgsql-sql
Is there a real quick way to do a query that will show me all the dates
given a startdate and an end date?
Given: 02-01-2006 and 02-28-2006
it should give me:
02-01-2006
02-02-2006
...
...
02-27-2006
02-28-2006
Can this be done by a built-in function perhaps?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-17 21:14 Owen Jacobson <ojacobson@osl.com>
0 siblings, 1 reply; 9+ messages in thread
From: Owen Jacobson @ 2006-02-17 21:14 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-17 21:20 Michael Fuhr <mike@fuhr.org>
parent: Henry Ortega <juandelacruz@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Fuhr @ 2006-02-17 21:20 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-17 23:08 Owen Jacobson <ojacobson@osl.com>
0 siblings, 1 reply; 9+ messages in thread
From: Owen Jacobson @ 2006-02-17 23:08 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
That usually indicates that, for whatever reason, plpgsql.so is from a different version of PostgreSQL than the database server. If you installed PostgreSQL from source, make sure you configured the server to look in the same lib dir as its libs were installed to; if you've installed from package management of some kind (RPM?) make sure you have the same 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 and performance 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-19 18:47 Henry Ortega <juandelacruz@gmail.com>
parent: Owen Jacobson <ojacobson@osl.com>
0 siblings, 2 replies; 9+ messages in thread
From: Henry Ortega @ 2006-02-19 18:47 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; +Cc: pgsql-sql
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 2/17/06, Owen Jacobson <ojacobson@osl.com> wrote:
>
> That usually indicates that, for whatever reason, plpgsql.so is from a
> different version of PostgreSQL than the database server. If you installed
> PostgreSQL from source, make sure you configured the server to look in the
> same lib dir as its libs were installed to; if you've installed from package
> management of some kind (RPM?) make sure you have the same 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 and performance 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-19 18:57 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2006-02-19 18:57 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-19 19:07 Michael Fuhr <mike@fuhr.org>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Fuhr @ 2006-02-19 19:07 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-20 20:30 Pedro B. <pedro.borracha@netcabo.pt>
parent: Owen Jacobson <ojacobson@osl.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pedro B. @ 2006-02-20 20:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Given 02-01-2006 to 02-28-2006, output all days.
@ 2006-02-20 20:39 Mark R. Dingee Pedro <mark.dingee@cox.net>
parent: Pedro B. <pedro.borracha@netcabo.pt>
0 siblings, 0 replies; 9+ messages in thread
From: "Mark R. Dingee" Pedro @ 2006-02-20 20:39 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-02-20 20:39 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-17 21:07 Given 02-01-2006 to 02-28-2006, output all days. Henry Ortega <juandelacruz@gmail.com>
2006-02-17 21:20 ` Michael Fuhr <mike@fuhr.org>
2006-02-17 21:14 Re: Given 02-01-2006 to 02-28-2006, output all days. Owen Jacobson <ojacobson@osl.com>
2006-02-20 20:30 ` Pedro B. <pedro.borracha@netcabo.pt>
2006-02-20 20:39 ` Mark R. Dingee Pedro <mark.dingee@cox.net>
2006-02-17 23:08 Re: Given 02-01-2006 to 02-28-2006, output all days. Owen Jacobson <ojacobson@osl.com>
2006-02-19 18:47 ` Henry Ortega <juandelacruz@gmail.com>
2006-02-19 18:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-19 19:07 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox