Re: Date Range Using Months and Days Only - Mailing list pgsql-general

Thanks for the reply and assistance. I share your concern that the approach
may be slow. I am not adverse to creating some sort of table to store the
time periods with years for the temporal extent of the data if that would
speed up the process. In that situation there would be multiple records (one
record for each year) that it would need to check against. I suppose I could
then create some sort of aggregate to then count the number of records in
which the date falls within the range. Anything over 0 would indicate that
the date does fall within the range?

From: Adam Cornett [mailto:adam.cornett@gmail.com]
Sent: Wednesday, October 19, 2011 6:51 PM
To: Jeff Adams
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Date Range Using Months and Days Only

On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams <Jeff.Adams@noaa.gov> wrote:
Greetings,

I have to write a query on a fairly large table of data (>100 million rows)
where I need to check to see if a date (epoch) falls between a range of
values. The catch is that the range is defined only by month and day values.
For example the record containing the epoch value will be linked to a table
that containing columns named start_month, start_day, end_month, end_day
that define the range. With respect to the range, year does not matter,
however, some of the ranges will start in November and end in April of the
next year. Has anyone come across this type of query? I could certainly
write a function or even include criteria in a query that would extract date
parts of the epoch and then compare against the values in the start_month,
start_day, end_month, end_day (it might get complex with respect to ranges
where the start year and end year are different), but I am worried about
performance. I thought I would seek some input before I floundered through
the many iterations of poor performing alternatives! Any thoughts would be
greatly appreciated.

Thanks in advance...
Jeff





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

create table a (
    id_a integer,
    epoch integer
);

create table b (
    id_b integer,
    start_month integer,
    start_day integer,
    end_month integer,
    end_day integer
);
CREATE OR REPLACE FUNCTION get_timestamps(sm integer, sd integer, em
integer, ed integer, year integer)
  RETURNS timestamp without time zone[] AS
$BODY$
declare
    syear integer := year;
    eyear integer := year;
    tstamps timestamp[];
begin
    if(sm>em) then
     -- assume that since the end month is less than the start month is in
the next year
     eyear := eyear+1;
    end if;
    tstamps[0] := (syear|| '-' || sm || '-' || sd)::timestamp;
    tstamps[1] := (eyear|| '-' || em || '-' || ed)::timestamp;
    return tstamps;

end
$BODY$
  LANGUAGE plpgsql STABLE;

create view a_timestamp as
SELECT id_a, TIMESTAMP WITH TIME ZONE 'epoch' + epoch * INTERVAL '1 second'
as tstamp from a;

with ab as(
select
id_a,id_b,get_timestamps(b.start_month,b.start_day,b.end_month,b.end_day,
extract(year from a.tstamp)::integer) ts_arr,a.tstamp
 from a_timestamp as a,b
)
select * from ab
where ab.tstamp between ts_arr[0] and ts_arr[1]

This obviously isn't a fast solution to your problem, although converting
the integer epoch to a timestamp in table a would eliminate the view
a_timestamp and you can index the column for some speed up, the real problem
you're facing is that your ranges don't have years, otherwise you could
store everything as a timestamp and then just join using 'between' and
postgres would just need to do an index scan on each table.

-Adam