Re: Date Range Using Months and Days Only
| От | Adam Cornett |
|---|---|
| Тема | Re: Date Range Using Months and Days Only |
| Дата | |
| Msg-id | CAB5sPxbpx3qxfzKbCZ5MLKgf3S7w4Bns5cQ3xvdkEeXG7wvKFA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Date Range Using Months and Days Only (Jeff Adams <Jeff.Adams@noaa.gov>) |
| Ответы |
Re: Date Range Using Months and Days Only
|
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: