From: Josh Berkus <josh@agliodbs.com>
To: Scott Gerhardt <scott@g-it.ca>
Cc: psql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
Date: Fri, 27 Aug 2004 13:19:41 -0700
Message-ID: <200408271319.41343.josh@agliodbs.com> (raw)
In-Reply-To: <CA23A046-F865-11D8-A763-000393801C60@g-it.ca>
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
<200408271159.26445.josh@agliodbs.com>
<CA23A046-F865-11D8-A763-000393801C60@g-it.ca>
Scott,
> Unfortunately, your revised query works like a charm except for the
> fact that prd_data."date" - prd2."date" + 1 give incorrect values when
> the year wraps, see in the output below. Need to conditionally
> subtract 88 from the date or use an incrementing count() function
> instead of date math to get the correct values.
Oh, that column is text, not a serial number. Well, it's your fault for
using wierd custom data types; you figure it out.
> I'm also wondering if using the date functions of Postgres would be
> helpful since the date column does have the YYYY-year and MM-month
> parts.
Well, you'd want to convert the column to a timestamp, and then you could
compute months. Or you could break it in seperate integer "year" and
"month" columns and do the same thing.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, scott@g-it.ca, psql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
In-Reply-To: <200408271319.41343.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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