Re: Date Math

On Mon, 7 May 2007, Tom Lane wrote:

> No, that's a truly awful way to do it.  The correct way is to use number
> times interval multiplication, eg
>
>     date_issued + term * '1 year'::interval;
>
> This reduces to not much more than a floating-point multiply, whereas
> the other way involves string-forming and string-parsing.  Plus you
> can easily use whatever multiplier you like, eg '7 days' if weeks
> strike your fancy.

   Thank you, Tom. This makes sense to me and I did not pick up on this in my
readings.

> It might be that converting those columns to interval is the best answer,
> depending on what other processing needs to be done with them. But if Rich
> wants to leave them as numbers, the above is the best way to convert them
> to intervals on-the-fly.

   No, we'll use whatever data type makes extracting rows the easiest and
most efficient.

   I don't see 'interval' as a data type in the docs. Is it a single-quoted
string? We can do converstions between the UI and storage (in both
directions), so the type in the DDL can be whatever's best.

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