Skip site navigation (1) Skip section navigation (2)

Re: Counting days ...

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: aarni(at)kymi(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Counting days ...
Date: 2008-03-14 15:59:49
Message-ID: 47DAA0F5.3010503@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Frank BaxDate: 2008-03-14 16:09:20
Subject: Re: Counting days ...
Previous:From: Aarni RuuhimäkiDate: 2008-03-14 15:13:40
Subject: Re: Counting days ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group