Thread: INTERVALs
============================================================================
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:
---------------------------------------------------------------------
??
> 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