============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name		:	Jose' Soares Da Silva
Your email address	:	sferac@bo.nettuno.it 


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: Intel Pentium

  Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.0.31 Elf

  PostgreSQL version (example: PostgreSQL-6.1)  : PostgreSQL-snapshot april 6, 1998

  Compiler used (example:  gcc 2.7.2)		: gcc 2.7.2.1


Please enter a FULL description of your problem:
------------------------------------------------

There are some bugs on INTERVALs...
...the keywords YEAR,MONTH,DAY,HOUR, MINUTE and SECOND must be specified
outside quotes not inside.

/*
INTERVAL year-month:
  written as the keyword INTERVAL, followed by a (year-month) interval string
  consisting of an opening single quote, an optional sign, either or both
  yyyy and mm (with a hyphen separator if both are specified), and closing
  single quote, followed by YEAR, MONTH or YEAR TO MONTH (as applicable).
  examples:
                INTERVAL '-1' YEAR;
                INTERVAL '2-6' YEAR TO MONTH;

*/
postgres=> SELECT INTERVAL '2-6' YEAR TO MONTH; <-- year to month outside '' ??
ERROR:  parser: parse error at or near "year"

postgres=> SELECT INTERVAL '2-6 YEAR TO MONTH';
ERROR:  Bad timespan external representation '2-6 YEAR TO MONTH'

/*
INTERVAL day-time:
  written as the keyword INTERVAL, followed by a (day-time) interval string
  consisting of an opening single quote, an optional sign, a contiguous
  nonempty subsequence of dd, hh, mm and ss[.[nnnnnn]] (with a space
  separator between dd and the rest, if dd is specified, and colon separators
  elsewhere), and a closing single quote, followed by the appropriate
  "start [TO end]" specification.
  examples:
               INTERVAL '2 12' DAY TO HOUR;
               INTERVAL '-4.50' SECOND;
*/
postgres=> SELECT INTERVAL '2 12 DAY TO HOUR' AS two_days_12_hrs;
two_days_12_hrs
---------------
@ 14 days            <--- this should be 2 days and 12 hours !!
(1 row)

postgres=> SELECT INTERVAL '-4.50 SECOND' AS four_sec_half_ago;
ERROR:  Bad timespan external representation '-4.50 SECOND'  
                                               ^^^^ decimal point ??

postgres=> SELECT INTERVAL '-4 SECOND' AS four_sec_half_ago;
four_sec_half_ag            ^^^ without decimal point it's ok.
-----------------
@ 4 secs ago
(1 row)
 
--arithmetic:

postgres=> SELECT INTERVAL '3 hour' / INTERVAL '1 hour';
?column?
--------
@ 3 secs        <---- why 3 secs ? It should be 3 hour !!
(1 row)

postgres=> SELECT INTERVAL '4 hour' * INTERVAL '3 hour';
ERROR:  There is no operator '*' for types 'timespan' and 'timespan'
        You will either have to retype this query using an explicit cast,
        or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT INTERVAL '4 hour' * 3;
ERROR:  There is no operator '*' for types 'timespan' and 'int4'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT INTERVAL '4 hour' / 2;
ERROR:  There is no operator '/' for types 'timespan' and 'int4'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT DATE '1998-07-31' + INTERVAL '1 MONTH';
ERROR:  There is no operator '+' for types 'date' and 'timespan'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT CURRENT_TIME + INTERVAL '1 HOUR';
ERROR:  There is no operator '+' for types 'time' and 'timespan'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT CURRENT_TIMESTAMP + INTERVAL '1 DAY';
ERROR:  There is no operator '+' for types 'timestamp' and 'timespan'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

postgres=> SELECT CURRENT_TIME - TIME '12:34';
ERROR:  There is no operator '-' for types 'time' and 'time'
	You will either have to retype this query using an explicit cast,
	or you will have to define the operator using CREATE OPERATOR

CREATE TABLE inter (
	inter1	INTERVAL YEAR,
	inter2	INTERVAL YEAR TO MONTH,
	inter3	INTERVAL MONTH,
	inter4	INTERVAL DAY,
	inter5	INTERVAL HOUR TO MINUTE,
	inter6	INTERVAL MINUTE TO SECOND,      <---error on this one.
ERROR:  parser: parse error at or near "to"
	inter7	INTERVAL DAY (3) TO SECOND (3)  <---error on this one.
);
ERROR:  parser: parse error at or near "("


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

??


Tue, 14 Apr 1998 10:13:07 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
INTERVALs
> There are some bugs on INTERVALs...
> ...the keywords YEAR,MONTH,DAY,HOUR, MINUTE and SECOND must be 
> specified outside quotes not inside.

The full syntax is supported for column declarations, but is not
supported for data entry. The problem is that this is the _only_ type
where the type specification appears on _both_ sides of the value! This
makes for really ugly syntax, and appears that it might vastly
complicate the parser. I'll admit I haven't spent much time looking at
that part of it though.

> postgres=> SELECT INTERVAL '2 12 DAY TO HOUR' AS two_days_12_hrs;
> two_days_12_hrs
> ---------------
> @ 14 days            <--- this should be 2 days and 12 hours !!
> (1 row)

The range is not really supposed to go inside the quotes, but for
historical reasons the parser is too forgiving and ignores things it
doesn't understand. That was for backward compatibility with v1.09/v6.0,
and perhaps we can tighten it up now...

> postgres=> SELECT INTERVAL '-4.50 SECOND' AS four_sec_half_ago;
> ERROR:  Bad timespan external representation '-4.50 SECOND'
>                                                ^^^^ decimal point ??

Thanks. I'll look at it. '4.5 secs ago' does work at the moment.

> --arithmetic:
> 
> postgres=> SELECT INTERVAL '3 hour' / INTERVAL '1 hour';
> ?column?
> --------
> @ 3 secs        <---- why 3 secs ? It should be 3 hour !!
> (1 row)

No, it should be "3" (no units). I was probably trying to do the right
thing with the "qualitative units" of year and month by keeping them
separate; instead, this should assume 30 days/month for the math and
return a double. Will look at it.

> postgres=> SELECT INTERVAL '4 hour' * INTERVAL '3 hour';
> ERROR:  There is no operator '*' for types 'timespan' and 'timespan'

Good. This would make no sense.

> postgres=> SELECT INTERVAL '4 hour' * 3;
> ERROR:  There is no operator '*' for types 'timespan' and 'int4'

Bad. This could make sense. Will put it on my list, and it may be helped
by my current project on type conversions.

> postgres=> SELECT INTERVAL '4 hour' / 2;
> ERROR:  There is no operator '/' for types 'timespan' and 'int4'

Ditto.

> postgres=> SELECT DATE '1998-07-31' + INTERVAL '1 MONTH';
> ERROR:  There is no operator '+' for types 'date' and 'timespan'

This works for DATETIME and INTERVAL. I'm currently working on the
automatic type conversion stuff, and it may help with this.

> postgres=> SELECT CURRENT_TIME + INTERVAL '1 HOUR';
> ERROR:  There is no operator '+' for types 'time' and 'timespan'

Hmm. But TIME is restricted to 0-23:59:59. I would have thought that
safe time arithmetic would need DATETIME or INTERVAL to allow overflow.
Do other systems implement this?

> postgres=> SELECT CURRENT_TIMESTAMP + INTERVAL '1 DAY';
> ERROR:  There is no operator '+' for types 'timestamp' and 'timespan'

TIMESTAMP does not have as many operators as DATETIME. They should
merge, unless there is a requirement that TIMESTAMP implement _all_ of
SQL92. That would damage it so much that we should leave DATETIME as the
more useful data type :(

> postgres=> SELECT CURRENT_TIME - TIME '12:34';
> ERROR:  There is no operator '-' for types 'time' and 'time'

Addition/subtraction on two absolute TIME fields does not make sense.
INTERVAL (or TIMESPAN) makes sense here as the second field. See above
comments on TIMESTAMP.

> CREATE TABLE inter (
>         inter1  INTERVAL YEAR,
>         inter2  INTERVAL YEAR TO MONTH,
>         inter3  INTERVAL MONTH,
>         inter4  INTERVAL DAY,
>         inter5  INTERVAL HOUR TO MINUTE,
>         inter6  INTERVAL MINUTE TO SECOND,      <---error on this one.
> ERROR:  parser: parse error at or near "to"

Omission. Will fix.

>         inter7  INTERVAL DAY (3) TO SECOND (3)  <---error on this one.
> );
> ERROR:  parser: parse error at or near "("

Yup.

> ??

A fundamental problem is that SQL92 has _really bad_ date/time datatypes
and features. We're trying to do better than that by having datatypes
which are more self consistant and capable. This may be a little
presumptuous, but what the heck :) However, I know that there is
interest in having full SQL92 compatibility, which is why TIMESTAMP has
not become as capable as DATETIME; I'm reluctant to merge them and then
be forced to damage the capabilities for compatibility reasons.

                      - Tom

Tue, 14 Apr 1998 15:29:20 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] INTERVALs