Date Math

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

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

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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

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

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

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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.

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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&gt; Voice: 503-667-4517 Fax: 503-667-8863

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

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&gt; Voice: 503-667-4517 Fax: 503-667-8863