Date Range Using Months and Days Only

From: Jeff Adams <Jeff(dot)Adams(at)noaa(dot)gov>
To: pgsql-general(at)postgresql(dot)org
Subject: Date Range Using Months and Days Only
Date: 2011-10-19 16:40:06
Message-ID: 001b01cc8e7d$c1fb9b90$45f2d2b0$%Adams@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Jaskiewicz 2011-10-19 16:56:24 Re: pg_dump not including custom CAST based on table types
Previous Message Merlin Moncure 2011-10-19 16:19:57 Re: Composite type, DEFAULT, NOT NULL, REFERENCES