|
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@xxxxxxx>
Sent: Friday, December 3, 2021 5:08 PM To: Tom Lane <tgl@xxxxxxxxxxxxx> Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx>
Sent: Friday, December 3, 2021 4:15 PM To: Sebastien Flaesch <sebastien.flaesch@xxxxxxx> Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxx> 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 |
