Обсуждение: How to specify infinity for intervals ?
I am storing the maximum age a vaccination is due in a patient (eg. "don't give this vaccination beyond the age of 10 years" or some such). Some vaccinations are to be given regardless of age. Up to now I have used NULL to mean "no maximum age". That doesn't really feel right and also complicates the SQL needed for retrieving data. I *could*, of course, use something like '999 years' as a special value to indicate "no upper limit" figuring that no one is going to live that long in the foreseeable future. However, the technically elegant and satisfying solution would be to be able to use "infinite" with interval data types much like "infinity" with timestamps. I have tried various syntax attempts, calculations and casts but haven't found any returning an interval of infinite length. The docs and Google don't help, either. I am running 7.4.9 on Debian 4.0. Anyone wants to comment/suggest something ? Thanks, Karsten GNUmed developer -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Feb 21, 2006, at 3:24 , Karsten Hilbert wrote:
> I am storing the maximum age a vaccination is due in a
> patient (eg. "don't give this vaccination beyond the age of
> 10 years" or some such). Some vaccinations are to be given
> regardless of age.
>
> Up to now I have used NULL to mean "no maximum age". That
> doesn't really feel right and also complicates the SQL
> needed for retrieving data.
I don't know the details of your database schema, but I think the
relationally proper way to do would be to have a separate table for
the maximum ages for vaccinations that have them. Vaccinations that
*don't* have a maximum age would not have an entry in the table. For
example:
create table vaccinations
(
vaccination text primary key
);
create table vaccination_max_ages
(
vaccination text primary key
references vaccinations (vaccination)
, maximum_age interval not null
);
This may make the SQL a bit more complicated, and you may end up
doing quite a few left joins which will give you NULLs anyway in the
result unless you use COALESCE. From one perspective (though not one
I necessarily agree with), using NULL to represent "no maximum age"
in the vaccinations table is a shorthand for this situation and
reduces the number of joins required.
The "special value" method, e.g., "999 years" is another way of
indicated a special value, but in this case I think it's a bit
different. As I see it, the predicate for the vaccination_max_ages
table is "The vaccination 'vaccination' must be given before the
patient is 'maximum_age'". Using a special value changes this
predicate to "The vaccination 'vaccination' can be given at any time
in the patient's life." As you point out, using a sufficiently large
interval for maximum_age makes that statement very likely to be true,
but the predicate is not exactly the same. Not having an entry in
vaccination_max_ages is much closer to the idea that the vaccination
has no maximum age.
That's the theory, anyway. Hope this helps a bit.
Currently on the todo list there's a mention of adding infinite
dates, similar to infinite timestamps. Perhaps infinite intervals
could be added as well?
Michael Glaesemann
grzm myrealbox com
On Tue, Feb 21, 2006 at 12:24:09PM +0900, Michael Glaesemann wrote:
> I don't know the details of your database schema,
If you want to you can look it up here:
http://salaam.homeunix.com/twiki/bin/view/Gnumed/DatabaseSchema
Feel free to comment !
> but I think the
> relationally proper way to do would be to have a separate table for
> the maximum ages for vaccinations that have them.
I know, but, oh no, yet another table ;-( And it would
also just lead to another form of NULL via left joins as you
point out below. I specifically wanted to avoid that by
something like 'infinite'::interval in some way or other
such that I could always do
"... where now < date_of_birth + max_age ..."
and not need an
"... or max_age is null ..."
in all the places.
> Vaccinations that
> *don't* have a maximum age would not have an entry in the table.
As you notice further down my predicate was wrong, actually.
You found the proper predicate by yourself, though:
"Do not care about the age of the patient when deciding
whether to give this vaccination."
> The "special value" method, e.g., "999 years" is another way of
> indicated a special value, but in this case I think it's a bit
> different. As I see it, the predicate for the vaccination_max_ages
> table is "The vaccination 'vaccination' must be given before the
> patient is 'maximum_age'". Using a special value changes this
> predicate to "The vaccination 'vaccination' can be given at any time
> in the patient's life." As you point out, using a sufficiently large
> interval for maximum_age makes that statement very likely to be true,
> but the predicate is not exactly the same. Not having an entry in
> vaccination_max_ages is much closer to the idea that the vaccination
> has no maximum age.
Well, but there's not really a medical difference between
the two AFAICT.
> That's the theory, anyway. Hope this helps a bit.
It confirms my thinking isn't entirely wrong.
> Currently on the todo list there's a mention of adding infinite
> dates, similar to infinite timestamps. Perhaps infinite intervals
> could be added as well?
I'd be delighted to have that happen.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Feb 22, 2006, at 1:51 , Karsten Hilbert wrote: > I specifically wanted to avoid that by > something like 'infinite'::interval in some way or other > such that I could always do > > "... where now < date_of_birth + max_age ..." > > and not need an > > "... or max_age is null ..." > > in all the places. I'd wrap it in an SQL function (untested): create function ok_to_vaccinate(date, interval) returns boolean language sql as' select current_timestamp < $1 + $2 or $2 is null '; Michael Glaesemann grzm myrealbox com
On Mon, Feb 20, 2006 at 19:24:05 +0100, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > > I *could*, of course, use something like '999 years' as a > special value to indicate "no upper limit" figuring that no > one is going to live that long in the foreseeable future. I would think that specifying a value well beyond the current life expectency for people would be the simplest solution. Since it doesn't have to be treated like a magic value and can used consistantly with other values in the same column.
On Mon, Feb 20, 2006 at 07:24:05PM +0100, Karsten Hilbert wrote: > I am storing the maximum age a vaccination is due in a > patient (eg. "don't give this vaccination beyond the age of > 10 years" or some such). Some vaccinations are to be given > regardless of age. > > Up to now I have used NULL to mean "no maximum age". That > doesn't really feel right and also complicates the SQL > needed for retrieving data. > > I *could*, of course, use something like '999 years' as a > special value to indicate "no upper limit" figuring that no > one is going to live that long in the foreseeable future. > > However, the technically elegant and satisfying solution > would be to be able to use "infinite" with interval data > types much like "infinity" with timestamps. I have tried > various syntax attempts, calculations and casts but haven't > found any returning an interval of infinite length. The docs > and Google don't help, either. I suspect that you could create either a domain or a custom type that would handle this they way you wanted. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Thanks to all for the suggestions. For the time being I will stay with using NULL. I will also stay with the hope that one day before long we will have "'infinite'::interval". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert wrote:
> Thanks to all for the suggestions.
>
> For the time being I will stay with using NULL.
>
> I will also stay with the hope that one day before long we
> will have "'infinite'::interval".
We have this TODO:
o Allow infinite dates just like infinite timestamps
Do we need to add intervals to this?
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
On Feb 25, 2006, at 12:09 , Bruce Momjian wrote: > We have this TODO: > > o Allow infinite dates just like infinite timestamps > > Do we need to add intervals to this? I think to be consistent with the other datetime types, might as well. Then one would be able to get an answer to test=# select 'infinity'::timestamp - current_timestamp; ERROR: cannot subtract infinite timestamps Michael Glaesemann grzm myrealbox com
OK, added to TODO. --------------------------------------------------------------------------- Michael Glaesemann wrote: > > On Feb 25, 2006, at 12:09 , Bruce Momjian wrote: > > > We have this TODO: > > > > o Allow infinite dates just like infinite timestamps > > > > Do we need to add intervals to this? > > I think to be consistent with the other datetime types, might as > well. Then one would be able to get an answer to > > test=# select 'infinity'::timestamp - current_timestamp; > ERROR: cannot subtract infinite timestamps > > Michael Glaesemann > grzm myrealbox com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. +
On Fri, Feb 24, 2006 at 10:09:25PM -0500, Bruce Momjian wrote: > Karsten Hilbert wrote: > > I will also stay with the hope that one day before long we > > will have "'infinite'::interval". > > We have this TODO: > > o Allow infinite dates just like infinite timestamps > > Do we need to add intervals to this? Yes. Thanks. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346