SUM() of INTERVAL type produces INTERVAL with no precision
Hello!
When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers...
Obviously one can cast the SUM() to get the original type back, but I was wondering if there is a simpler way to handle this, to not force programmers to add a CAST() or :: all over the place.
The type of a SELECT item is important to us because we have generic C code that identifies the PQfmod() type, to properly interpret the string value returned by PQgetvalue().
With SUM(interval-column), PQfmod() returns -1 ...
Here some SQL sample to illustrate our concern by describing column types:
=======================================================================
create table tab1 ( pkey int, dur interval hour to second(0));
\d tab1
insert into tab1 values ( 101, interval '999:12:45');
insert into tab1 values ( 102, interval '100:00:00');
create view v1 as select sum(dur) from tab1;
select * from v1;
\d v1
create view v2 as select sum(dur)::interval hour to second(0) from tab1;
select * from v2;
\d v2
drop view v1;
drop view v2;
drop table tab1;
=======================================================================
Output:
CREATE TABLE
Table "public.tab1"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
pkey | integer | | |
dur | interval hour to second(0) | | |
INSERT 0 1
INSERT 0 1
CREATE VIEW
sum
------------
1099:12:45
(1 row)
View "public.v1"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
sum | interval | | |
CREATE VIEW
sum
------------
1099:12:45
(1 row)
View "public.v2"
Column | Type | Collation | Nullable | Default
--------+----------------------------+-----------+----------+---------
sum | interval hour to second(0) | | |
DROP VIEW
DROP VIEW
DROP TABLE
Why is PostgreSQL "losing" the original type when doing aggregates like SUM()?
Seems the result can fit into an INTERVAL HOUR TO SECOND(0) since we can cast...
With other database engines like Informix, one defines a dimension for the largest interval qualifier:
INTERVAL HOUR(9) TO SECOND => can store 123456789:00:00 for ex
Is this is handled differently in PostgreSQL, right?
Sorry to ask if this is obvious to experts, but the documentation should be a bit more detailed for the interval type and its storage possibilities.
https://www.postgresql.org/docs/14/datatype-datetime.html
Seb
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers...
We don't generally attribute a typmod (which is what interval field
specs are) to the output of any function, other than the ones that are
specifically identified in the catalogs as cast-to-a-particular-typmod
infrastructure. Why do you care? The value is the same regardless.
The type of a SELECT item is important to us because we have generic C code that identifies the PQfmod() type, to properly interpret the string value returned by PQgetvalue().
I think you have vastly overestimated the value of PQfmod. Moreover,
if you think you need it to interpret the output data, you are mistaken.
There is no situation in which Postgres output formats are
typmod-specific; in fact, the output function API doesn't even pass
the typmod.
regards, tom lane
Hi Tom,
I do care because I wrote a database driver/interface that needs to do automatic conversions from native PostgreSQL types, to our programming language types (we sell a compile/runtime system).
I need introspection of the fetched data. The more exact type info I get, the better.
As our software is a programming language runtime system, I don't know in advance the SQL statement and column types.
It's all generic code.
We have features/APIs that return the type of a SELECT item, like ODBC's SQLDescribeCol() ...
Our programming language supports Informix-style INTERVALs where you have 2 classes:
INTERVAL year-to-month
INTERVAL day-to-second[.fractions]
It's not easy to explain all the constraints here in a few lines but basically, I can better handle the values returned from PQgetvalue(), when I know exactly when is the original type from PQftype() and PQfmode()
We could help our customer by using a CAST():
SELECT CAST( SUM(col) AS INTERVAL HOUR TO MINUTE) ...
After more tests:
I have some doubts about the values that can be inserted into a PostgreSQL INTERVAL.
Sorry if I am missing something, but it's quite strange to me that one can insert various "classes" of interval values:
test1=> create table tab1 ( i interval );
CREATE TABLE
test1=> insert into tab1 values ( interval '12434:12:11' );
INSERT 0 1
test1=> select * from tab1;
i
-------------
12434:12:11
(1 row)
test1=> insert into tab1 values ( interval '99999 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
---------------------
12434:12:11
99999 days 12:11:10
(2 rows)
test1=> insert into tab1 values ( interval '9999-10 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
-----------------------------
12434:12:11
99999 days 12:11:10
9999 years 10 mons 12:11:10
(3 rows)
test1=> insert into tab1 values ( interval '9999-10 55 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
-------------------------------------
12434:12:11
99999 days 12:11:10
9999 years 10 mons 12:11:10
9999 years 10 mons 55 days 12:11:10
(4 rows)
How much days is is 9999 years 10 months 55 days?
A month can have 30, 31, 28 or 29 days ...
I must carefully read the doc again.
Thanks for you answer(s)
Seb
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, December 3, 2021 4:15 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers...
We don't generally attribute a typmod (which is what interval field
specs are) to the output of any function, other than the ones that are
specifically identified in the catalogs as cast-to-a-particular-typmod
infrastructure. Why do you care? The value is the same regardless.
The type of a SELECT item is important to us because we have generic C code that identifies the PQfmod() type, to properly interpret the string value returned by PQgetvalue().
I think you have vastly overestimated the value of PQfmod. Moreover,
if you think you need it to interpret the output data, you are mistaken.
There is no situation in which Postgres output formats are
typmod-specific; in fact, the output function API doesn't even pass
the typmod.
regards, tom lane
Hum....
test1=> create table tab2 ( i interval hour to minute );
CREATE TABLE
test1=> insert into tab2 values ( interval '9999-10 555 11:22:33' );
INSERT 0 1
test1=> select * from tab2;
i
--------------------------------------
9999 years 10 mons 555 days 11:22:00
(1 row)
Expected?
Qualifiers "hour to minute" seem just to be some decoration...
Seb
________________________________
From: Sebastien Flaesch <sebastien.flaesch@4js.com>
Sent: Friday, December 3, 2021 5:08 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Hi Tom,
I do care because I wrote a database driver/interface that needs to do automatic conversions from native PostgreSQL types, to our programming language types (we sell a compile/runtime system).
I need introspection of the fetched data. The more exact type info I get, the better.
As our software is a programming language runtime system, I don't know in advance the SQL statement and column types.
It's all generic code.
We have features/APIs that return the type of a SELECT item, like ODBC's SQLDescribeCol() ...
Our programming language supports Informix-style INTERVALs where you have 2 classes:
INTERVAL year-to-month
INTERVAL day-to-second[.fractions]
It's not easy to explain all the constraints here in a few lines but basically, I can better handle the values returned from PQgetvalue(), when I know exactly when is the original type from PQftype() and PQfmode()
We could help our customer by using a CAST():
SELECT CAST( SUM(col) AS INTERVAL HOUR TO MINUTE) ...
After more tests:
I have some doubts about the values that can be inserted into a PostgreSQL INTERVAL.
Sorry if I am missing something, but it's quite strange to me that one can insert various "classes" of interval values:
test1=> create table tab1 ( i interval );
CREATE TABLE
test1=> insert into tab1 values ( interval '12434:12:11' );
INSERT 0 1
test1=> select * from tab1;
i
-------------
12434:12:11
(1 row)
test1=> insert into tab1 values ( interval '99999 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
---------------------
12434:12:11
99999 days 12:11:10
(2 rows)
test1=> insert into tab1 values ( interval '9999-10 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
-----------------------------
12434:12:11
99999 days 12:11:10
9999 years 10 mons 12:11:10
(3 rows)
test1=> insert into tab1 values ( interval '9999-10 55 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
i
-------------------------------------
12434:12:11
99999 days 12:11:10
9999 years 10 mons 12:11:10
9999 years 10 mons 55 days 12:11:10
(4 rows)
How much days is is 9999 years 10 months 55 days?
A month can have 30, 31, 28 or 29 days ...
I must carefully read the doc again.
Thanks for you answer(s)
Seb
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, December 3, 2021 4:15 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers...
We don't generally attribute a typmod (which is what interval field
specs are) to the output of any function, other than the ones that are
specifically identified in the catalogs as cast-to-a-particular-typmod
infrastructure. Why do you care? The value is the same regardless.
The type of a SELECT item is important to us because we have generic C code that identifies the PQfmod() type, to properly interpret the string value returned by PQgetvalue().
I think you have vastly overestimated the value of PQfmod. Moreover,
if you think you need it to interpret the output data, you are mistaken.
There is no situation in which Postgres output formats are
typmod-specific; in fact, the output function API doesn't even pass
the typmod.
regards, tom lane
On 12/3/21 08:12, Sebastien Flaesch wrote:
Hum....
test1=> create table tab2 ( i interval hour to minute );
CREATE TABLE
test1=> insert into tab2 values ( interval '9999-10 555 11:22:33' );
INSERT 0 1
test1=> select * from tab2;
i
--------------------------------------
9999 years 10 mons 555 days 11:22:00
(1 row)Expected?
Qualifiers "hour to minute" seem just to be some decoration...
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
"... Also, field values “to the right” of the least significant field
allowed by the fields specification are silently discarded. For example,
writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
seconds field, but not the day field."
Seb
------------------------------------------------------------------------
--
Adrian Klaver
adrian.klaver@aklaver.com
My bad, sorry. RTFM Seb.
Seb
________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, December 3, 2021 5:30 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
On 12/3/21 08:12, Sebastien Flaesch wrote:
Hum....
test1=> create table tab2 ( i interval hour to minute );
CREATE TABLE
test1=> insert into tab2 values ( interval '9999-10 555 11:22:33' );
INSERT 0 1
test1=> select * from tab2;
i
--------------------------------------
9999 years 10 mons 555 days 11:22:00
(1 row)Expected?
Qualifiers "hour to minute" seem just to be some decoration...
"... Also, field values “to the right” of the least significant field
allowed by the fields specification are silently discarded. For example,
writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
seconds field, but not the day field."
Seb
------------------------------------------------------------------------
--
Adrian Klaver
adrian.klaver@aklaver.com