Обсуждение: Date Math
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
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> 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> 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> 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> 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> 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> 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> 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> Voice: 503-667-4517 Fax: 503-667-8863