Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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,
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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 $$
declareaa date:=start_date;But I always get thisERROR: parser: parse error at or near DATE at character 33before I can even finish.Any idea why this happens?
On 2/17/06, Owen Jacobson [EMAIL PROTECTED] 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:[EMAIL PROTECTED]]Sent: Friday, February 17, 2006 2:06 PMTo: Owen JacobsonSubject: 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 failedI have pgsql 7.3.2I am googling and can't seem to find the answer. Any help would be appreciated.On 2/17/06, Owen Jacobson
[EMAIL PROTECTED] 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 $$declarecurdate date;begincurdate := start;while (curdate = finish) loopreturn next curdate;curdate := curdate + 1;
end loop;return;end;$$ language plpgsql;# select * from days ('2006-02-01', '2006-02-07');days2006-02-012006-02-022006-02-032006-02-042006-02-052006-02-06
2006-02-07(7 rows)---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 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 given a startdate and an end date?Given: 02-01-2006 and 02-28-2006it should give me:02-01-200602-02-2006..02-27-2006 02-28-2006Can this be done by a built-in function perhaps?
Re: [SQL] 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)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
