Re: [HACKERS] INTERVALs - Mailing list pgsql-hackers

> 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