Subject: Postgres 4.0.1, having problems with dates and indexing

From: Aaron Roydhouse <[email-masked]>
Date: Tue, 03 Aug 1993 09:54:49 +1200

I am using Postgres 4.0.1 under SunOS 4.1.3 on a SparcStation 10. I
compiled using the "sparc" port and in most ways Postgres is behaving
well. However I seem to have problems with the built-in abstime,
reltime, and tinterval types. I think some reltime code malfunction is
at the root of the problem. Here's a summary.

1. Date arithmetic doesn't work for me. abstime comparative operators
work, but anything to do with the reltime type doesn't (e.g. abstime
- reltime always produces and abstime of 31 minutes past some hour
in the next millennium!

2. I can get tinterval to work at all. I finally worked out the
correct syntax from the source ("['...' '...']", yes?) but whatever I
supply is considered an invalid interval.

3. Sometimes btree indexes using dateops don't work. That is, say you
have a query Q with produces the desired result on unindexed result on
table T, If you then define and index I for an attribute of table T
then query Q stops working (it always selects zero tuples --- much
more quickly though :-).

An earlier post mentions a version 4.1, is there a newer release that
4.0.1 available? Any comments at all would be appreciated, and
particularly pointers to FAQ information or sample applications using
Postgres.

Thanks,
Aaron.

---

From: Avi Pfeffer <[email-masked]>
Date: Wed, 4 Aug 93 13:58:14 -0700

Aaron, a couple of comments.

First of all, "1 hour" is not the correct syntax for reltimes.  You
need to say "@ 1 hour".  Also, there was a bug of unknown vintage that
made timemi and timepl expect to take two abstimes.  The reason the
bug wasn't discovered till now is that most people use the + and -
operators, and the operators were defined correctly.  You will also
probably find it easier to use them.

I have absolutely no idea why the tinterval query should have caused
postgres to crash.  Try it again with 4.1.  It works fine for me.

avi

Script follows :
Welcome to the POSTGRES terminal monitor

Go 
* retrieve (x = "Aug 12 10:00:00 1990 GMT"::abstime + "@ 1 hour"::reltime)
\g
Query sent to backend is "retrieve (x = "Aug 12 10:00:00 1990 GMT"::abstime + "@ 1 hour"::reltime)  retrieve (x = "Aug 12 10:00:00 1990 GMT"::abstime - "@ 1 hour"::reltime)  retrieve (x = "['Aug 12 10:00:00 1990 GMT' 'Aug 14 10:00:00 1990 GMT']"::tinterval)"
---------------
| x           |
---------------
| Sun Aug 12 03:00:00 1990 PDT|
---------------

Go
* retrieve (x = "Aug 12 10:00:00 1990 GMT"::abstime - "@ 1 hour"::reltime)
\g
---------------
| x           |
---------------
| Sun Aug 12 01:00:00 1990 PDT|
---------------

Go
* retrieve (x = "['Aug 12 10:00:00 1990 GMT' 'Aug 14 10:00:00 1990 GMT']"::tinterval)\g
\g
---------------
| x           |
---------------
| ['Sun Aug 12 02:00:00 1990 PDT' 'Tues Aug 14 02:00:00 1990 PDT']|
---------------

Go 
*