Re: getting dates in the past - Mailing list pgsql-general

On Sep 26, 2006, at 5:59 , garry saddington wrote:

> How could I translate this into sql?
>
> select result from results
> where date_entered between (last september and the one before that)
>
> Its the part in brackets that has me guessing. I am still
> experimenting
> but any help will be gratefully recieved.

For me, the question is how do you define last_september. It being
2006-09-26 at the time of this writing, is last_september September,
2005? Assuming last_september is September, 2005, are you looking for
any date in the range (inclusive) [2004-09-01, 2005-09-30] or
[2004-10-01, 2005-08-31] or something else? Once you decide that, you
can do some date math to determine the bounds of the range. And you
could wrap it in an SQL function to keep it tidy.

Here's one way, assuming any date between 2004-09-01 and 2005-09-30
is okay for a query run on 2006-09-26:

create or replace function
date_between_last_specified_month_and_year_before(
     date -- $1 date you're interested in comparing
     , date -- $1 date during "last whenever month"
     ) returns boolean
strict
security definer
language sql as $func$
select
     case when extract(month from current_date) >= extract(month from
$2)
     then
         $1 between (date_trunc('month',$2) - interval '2 years')::date
             and (date_trunc('month', $2) - interval '11 months 1
day')::date
     else
         $1 between (date_trunc('month',$2) - interval '1 year')::date
             and (date_trunc('month',$2) + interval '1 month -1
day')::date
     end;
$func$;

I believe the SQL function will be inlined, so it should be
equivalent in terms of performance to writing out the case statement
explicitly in the SQL query.

create table dates_entered(date_entered date primary key);
copy dates_entered from stdin;
2004-08-31
2004-09-01
2004-09-30
2004-10-01
2005-08-31
2005-09-01
2005-09-30
2005-10-01
2006-08-31
2006-09-01
2006-09-30
2006-10-01
\.


-- valid range should be 2004-08-01 through 2005-08-31
select *
from dates_entered
where date_between_last_specified_month_and_year_before
(date_entered,'2006-08-15');
date_entered
--------------
2004-08-31
2004-09-01
2004-09-30
2004-10-01
2005-08-31
(5 rows)

-- valid range should be 2003-09-01 through 2004-09-30
select *
from dates_entered
where date_between_last_specified_month_and_year_before
(date_entered,'2005-09-15');
date_entered
--------------
2004-08-31
2004-09-01
2004-09-30
(3 rows)

-- valid range should be 2004-09-01 through 2005-09-30
select *
from dates_entered
where date_between_last_specified_month_and_year_before
(date_entered,'2006-09-15');
date_entered
--------------
2004-09-01
2004-09-30
2004-10-01
2005-08-31
2005-09-01
2005-09-30
(6 rows)

-- valid range should be 2005-09-01 through 2006-09-30
select *
from dates_entered
where date_between_last_specified_month_and_year_before
(date_entered,'2007-09-15');
date_entered
--------------
2005-09-01
2005-09-30
2005-10-01
2006-08-31
2006-09-01
2006-09-30
(6 rows)

Hope this helps.

Michael Glaesemann
grzm seespotcode net