Re: Date Math

-------------- Original message ----------------------
From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
> I've read both Section 8.5 of the on-line 8.2.4 docs, and the pertinent
> sections of Douglas & Douglas, and I'm still not certain that I'm correctly
> expressing the query I want. Please correct as needed.
>
> From table (Permits) I want to identify those which expire within a
> specified time from today. For example:
>
> SELECT permit_nbr, title, date_issued, term,
> process_time from Permits
> WHERE (date_issued + term YEARS)
> < (CURRENT_DATE + process_time MONTHS);
>
> Should I use TODAY rather than CURRENT_DATE? Do I need to cast intervals
> explicitly from seconds to days, months, or years?
>

Are you thinking something like the following-

test=> select '01/01/04'::date +interval '3 year',current_date + interval '2
month';
?column? | ?column?
---------------------+---------------------
2007-01-01 00:00:00 | 2007-07-07 00:00:00
(1 row)

test=> select '01/01/04'::date +interval '3 year'<current_date + interval '2
month';
?column?
----------
t
(1 row)

Adrian Klaver
aklaver(at)comcast(dot)net

On Mon, 7 May 2007, aklaver(at)comcast(dot)net wrote:

> test=> select '01/01/04'::date +interval '3 year',current_date + interval
> '2 month';
> ?column? | ?column?
> ---------------------+---------------------
> 2007-01-01 00:00:00 | 2007-07-07 00:00:00
> (1 row)

Adrian,

I think so, but without explicit strings. The dates and intervals are in
the table, and I want the rows that meet the specified conditions.

Is the following closer to correct?

SELECT ... FROM Permits
WHERE (date_issued::DATE + INTERVAL term)
< (CURRENT_DATE + INTERVAL process_time + INTERVAL '2 week')

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863