Thread: Date Math

Date Math

From
Rich Shepard
Date:
   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?

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

Re: Date Math

From
"A. Kretschmer"
Date:
am  Mon, dem 07.05.2007, um  9:43:50 -0700 mailte Rich Shepard folgendes:
>   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?

I'm not sure if I understand you correctly, if not, sorry.
I think, you should cast your intervals, an example:

select current_date + '10 months'::interval;

You syntax above are wrong.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Date Math

From
aklaver@comcast.net
Date:
 -------------- Original message ----------------------
From: Rich Shepard <rshepard@appl-ecosys.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@comcast.net

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, A. Kretschmer wrote:

> I think, you should cast your intervals, an example:
>
> select current_date + '10 months'::interval;

Andreas,

   OK. I wasn't clear on this point.

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

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, aklaver@comcast.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

Re: Date Math

From
aklaver@comcast.net (Adrian Klaver)
Date:
 -------------- Original message ----------------------
From: Rich Shepard <rshepard@appl-ecosys.com>
> On Mon, 7 May 2007, aklaver@comcast.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

If term and process_time are stored as intervals then it will work. Also if they are stored as
INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the
INTERVAL . Is date_issued stored as a date? If so it would not need to be cast.
--
Adrian Klaver
aklaver@comcast.net

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, Adrian Klaver wrote:

> If term and process_time are stored as intervals then it will work. Also
> if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In
> other words not have to declare the INTERVAL . Is date_issued stored as a
> date? If so it would not need to be cast.

Adrian,

   Here are the pertinent declarations in the DDL:

   date_issued DATE NOT NULL
     CONSTRAINT invalid_date
       CHECK (date_applied <= date_issued),
   term SMALLINT DEFAULT 1 NOT NULL,                  -- in years
   processing_time DEFAULT 180 NOT NULL SMALLINT,     -- in days

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

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, Rich Shepard wrote:

>  term SMALLINT DEFAULT 1 NOT NULL,                  -- in years
>  processing_time DEFAULT 180 NOT NULL SMALLINT,     -- in days

   I can change from SMALLINT to INT4 if that helps clarify the values as
INTERVALs.

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

Re: Date Math

From
Adrian Klaver
Date:
On Monday 07 May 2007 10:56 am, Rich Shepard wrote:
> On Mon, 7 May 2007, Adrian Klaver wrote:
> > If term and process_time are stored as intervals then it will work. Also
> > if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In
> > other words not have to declare the INTERVAL . Is date_issued stored as a
> > date? If so it would not need to be cast.
>
> Adrian,
>
>    Here are the pertinent declarations in the DDL:
>
>    date_issued DATE NOT NULL
>      CONSTRAINT invalid_date
>        CHECK (date_applied <= date_issued),
>    term SMALLINT DEFAULT 1 NOT NULL,                  -- in years
>    processing_time DEFAULT 180 NOT NULL SMALLINT,     -- in days
>
> Thanks,
>
> Rich
With this setup you will have to use an explicit string-
date_issued + INTERVAL  term|| 'years'. This will involve constructing a
string and passing it to INTERVAL.  The alternative is to change the column
types of term and processing_time to interval and store the interval period
with the interval qty i.e '1 year' for term and '400 days' for processing
time for example. This way the you can use the values directly without
invoking INTERVAL.

--
Adrian Klaver
aklaver@comcast.net

Re: Date Math

From
Tom Lane
Date:
Adrian Klaver <aklaver@comcast.net> writes:
> With this setup you will have to use an explicit string-
> date_issued + INTERVAL  term|| 'years'. This will involve constructing a
> string and passing it to INTERVAL.

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.

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.

            regards, tom lane

Re: Date Math

From
Adrian Klaver
Date:
On Monday 07 May 2007 12:00 pm, Tom Lane wrote:
> Adrian Klaver <aklaver@comcast.net> writes:
> > With this setup you will have to use an explicit string-
> > date_issued + INTERVAL  term|| 'years'. This will involve constructing a
> > string and passing it to INTERVAL.
>
> 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.
>
> 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.
>
>             regards, tom lane
Yea, I realized the error of my ways after hitting send. An ounce of proof
reading prevents a pound of oops.
--
Adrian Klaver
aklaver@comcast.net

Re: Date Math

From
Rich Shepard
Date:
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

Re: Date Math

From
Richard Broersma Jr
Date:
>    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.

It is shown as the 4th item on table 8-9:
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html

Regards,
Richard Broermsa Jr.

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, Richard Broersma Jr wrote:

> It is shown as the 4th item on table 8-9:
> http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html

   D'oh! Of course I saw that, but it did not register with me.

Thanks, Richard!

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

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, Tom Lane wrote:

> 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.

   Columns 'term' and 'process_time' converted to intervals. Just to confirm
my understanding of today's lesson:

     SELECT permit_nbr, title, date_issued, term, process_time from Permits
            WHERE date_issued + term) < (CURRENT_DATE + process_time + '2 week'::INTERVAL);

is now correct syntax and use?

Thanks all,

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

Re: Date Math

From
Adrian Klaver
Date:
On Monday 07 May 2007 3:19 pm, Rich Shepard wrote:
> On Mon, 7 May 2007, Tom Lane wrote:
> > 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.
>
>    Columns 'term' and 'process_time' converted to intervals. Just to
> confirm my understanding of today's lesson:
>
>      SELECT permit_nbr, title, date_issued, term, process_time from Permits
>             WHERE date_issued + term) < (CURRENT_DATE + process_time + '2
> week'::INTERVAL);
>
> is now correct syntax and use?
>
> Thanks all,
>
> Rich
Yes.
--
Adrian Klaver
aklaver@comcast.net

Re: Date Math

From
Rich Shepard
Date:
On Mon, 7 May 2007, Adrian Klaver wrote:

> Yes.

Adrian,

   Whew! :-)

   Now I'm working on pulling dates from two tables and checking if they're
in the current quarter. I see that I need SELECT EXTRACT FROM <column_name>,
and I'm thinking how to incorporate this with the rest of the selection
criteria. I'll probably be back on the list tomorrow.

Thanks very much,

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