From: Steve Crawford <scrawford@pinpointresearch.com>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 08:59:49 -0700
Message-ID: <47DAA0F5.3010503@pinpointresearch.com> (raw)
In-Reply-To: <200803141504.54211.aarni@kymi.com>
References: <200803132025.27821.aarni@kymi.com>
<200803132209.33914.aarni@kymi.com>
<47D98D11.4080406@pinpointresearch.com>
<200803141504.54211.aarni@kymi.com>
Aarni Ruuhimäki wrote:
>
> Thanks Steve,
>
> I'm not sure if I quite grasped this. It gives a bit funny results:
>
> SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) -
> date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS
> days_in_period,
> c.country_name AS country
> FROM product_res pr
> LEFT JOIN countries c ON pr.country_id = c.country_id
> WHERE group_id = 1 AND res_end_day >= '2007-01-01' AND res_end_day <=
> '2008-12-31' group by pr.country_id, c.country_name;
> days_in_period | country
> ----------------+--------------------
> -441137 |
> -30 | Germany
> -28 | Estonia
> ...
I see one error in my logic. It doesn't account for the situation where
res_end_day is prior to the start of the period you are viewing. You can
fix this by limiting records with the appropriate where-clause or by
wrapping the date_smaller inside a date_larger (and vice-versa) to
ensure that all dates stay inside the desired period.
Or you can fix it by using an appropriate where-clause. Yours appears
broken - I think you want res_end_day >2006-12-31 (or >=2007-01-01 - I
prefer mine as you can use the same date in multiple places in the
query) which is what you have.
But I think you want the end of period to be limited to res_start_day
<=2007-12-31.
IOW, if your *end* date is *before* the period of interest or your
*start* date is *after* the period of interest, skip the record.
My guess is that you have records with res_start_day > 2007-12-31. After
applying the larger and smaller functions, this will end up with a
res_end_day of 2007-12-31 giving an end_day < start_day.
(I'm presuming you have appropriate constraints to prevent end_day from
being earlier than start_day. If not, check for that and add the
constraints.)
Cheers,
Steve
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: scrawford@pinpointresearch.com, aarni@kymi.com
Subject: Re: Counting days ...
In-Reply-To: <47DAA0F5.3010503@pinpointresearch.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