Thread: A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
While I do understand that the BETWEEN operator is actually "synctactic sugar", from time to time I find myself wondering about a better BETWEEN for DATEs, TIMEs and TIMESTAMPs (but not only these ones). Infact I always have managed ranges where the lower part is to be matched with the ">=" comparison operator while the higher part is matched with the "<". In other words, until now I've always encountered intervals of the type "[...)" and not "[...]". Wouldn't it be nice to have a version of the BETWEEN operator which uses such a kind of intervals? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
On Wed, 2009-01-14 at 11:40 +0100, Reg Me Please wrote:
> Infact I always have managed ranges where the lower part is to be matched with
> the ">=" comparison operator while the higher part is matched with the "<".
> In other words, until now I've always encountered intervals of the
> type "[...)" and not "[...]".
>
> Wouldn't it be nice to have a version of the BETWEEN operator which uses
> such a kind of intervals?
I think the best solution is to make first-class interval types (for
time as well as other types). Those intervals can then have operators
like "contains" and "contained by" which would solve your problem.
Additionally, it would allow lots of other interesting operations, like
overlaps and intersects.
Regards,
Jeff Davis
On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: > I think the best solution is to make first-class interval types (for > time as well as other types). Those intervals can then have operators > like "contains" and "contained by" which would solve your problem. > > Additionally, it would allow lots of other interesting operations, like > overlaps and intersects. I wrote such an interval type here, called "period" (to avoid confusion with the SQL INTERVAL type): http://pgfoundry.org/projects/temporal Regards, Jeff Davis
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: > On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: > > I think the best solution is to make first-class interval types (for > > time as well as other types). Those intervals can then have operators > > like "contains" and "contained by" which would solve your problem. > > > > Additionally, it would allow lots of other interesting operations, like > > overlaps and intersects. > > I wrote such an interval type here, called "period" (to avoid confusion > with the SQL INTERVAL type): > > http://pgfoundry.org/projects/temporal > > Regards, > Jeff Davis Jeff, I'll give your implementation a try and possibly a look to the code itself. I thought that was not just my personal opinion, though. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: > On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: > > I think the best solution is to make first-class interval types (for > > time as well as other types). Those intervals can then have operators > > like "contains" and "contained by" which would solve your problem. > > > > Additionally, it would allow lots of other interesting operations, like > > overlaps and intersects. > > I wrote such an interval type here, called "period" (to avoid confusion > with the SQL INTERVAL type): > > http://pgfoundry.org/projects/temporal > > Regards, > Jeff Davis I got some compilation error (I run Ubuntu) that needs investigation. From the documentation it seems exactly what I was looking for! I would put this thing among the official contribs: any chance? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
On Wed, 2009-01-14 at 22:59 +0100, Reg Me Please wrote:
> I got some compilation error (I run Ubuntu) that needs investigation.
> From the documentation it seems exactly what I was looking for!
Please discuss my module in the mailing list for the project itself, or
email me directly with more details (e.g. what commands you ran).
> I would put this thing among the official contribs: any chance?
>
I am working on some supporting code that might be included in
PostgreSQL and allow my module to support even more useful functions.
Until then, it can probably live on pgfoundry separately. My
understanding is that contrib is not meant to include every module
that's useful, but only those that have a reason to be distributed and
released jointly with PostgreSQL.
If I write new operators or fix a bug, I think it's best to be able to
distribute that immediately rather than waiting for PostgreSQL to do
another release.
Regards,
Jeff Davis