Question:
I'm trying to use crosstab in postgres but…
CREATE TABLE tb_testect
(
datahora timestamp without time zone,
teste integer
);
* Correction
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 08:30:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 09:45:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 15:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 18:00:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 20:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 21:00:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 11:30:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 12:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 16:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 17:30:00',2);
select * from tb_testect;
* Correction
datahora | teste
--------------------+-------
2016-01-01 08:30:00 | 1
2016-01-01 09:45:00 | 2
2016-01-02 15:30:00 | 2
2016-01-02 18:00:00 | 1
2016-01-02 20:30:00 | 2
2016-01-02 21:00:00 | 1
2016-01-01 11:30:00 | 1
2016-01-01 12:30:00 | 2
2016-01-01 16:30:00 | 2
2016-01-02 17:30:00 | 2
When I run the following SQL:
SELECT * FROM crosstab('
select teste, date(datahora), "time"(datahora)
from tb_testect ORDER BY 1,2')
AS ct ("teste" int, "data" timestamp , "hora1" timestamp, "hora2" timestamp);
I would like the feedback to be:
* Correction
teste | data | hora1 | hora2 | hora3
-------+------------+----------+----------+---------
1 | 2016-01-01 | 08:30:00 | 11:30:00 |
1 | 2016-01-02 | 18:45:00 | 21:00:00 |
2 | 2016-01-01 | 18:00:00 | 21:00:00 | 16:30:00
2 | 2016-01-02 | 13:30:00 | 20:30:00 | 17:30:00
But what is returning is the following error:
ERROR: return and sql tuple descriptions are incompatible
* Fix : I need to show hour1, hour2, hour… of the days 2016-01-01, 2016-01-02,…
Any idea what I might be doing wrong? Thanks.
Answer:
The second column of the query given to the crosstab exists for sorting only and is not returned. In your case, just extract the date from one of the returned timestamp columns:
select teste, hora1::date as data, hora1::time, hora2::time
from
crosstab ('
select teste, date(datahora), datahora
from tb_testect
order by 1,2
') as ct (teste int, hora1 timestamp, hora2 timestamp )
;
teste | data | hora1 | hora2
-------+------------+----------+----------
1 | 2016-01-01 | 08:30:00 | 18:00:00
2 | 2016-01-01 | 09:45:00 | 15:30:00
New
With the review of the question it seems that what you want is not crosstab but grouping:
select
teste, datahora::date as data,
min(datahora)::time as hora1, max(datahora)::time as hora2
from tb_testect
group by 1,2
order by 1,2
;
teste | data | hora1 | hora2
-------+------------+----------+----------
1 | 2016-01-01 | 08:30:00 | 11:30:00
1 | 2016-01-02 | 18:00:00 | 21:00:00
2 | 2016-01-01 | 09:45:00 | 12:30:00
2 | 2016-01-02 | 15:30:00 | 20:30:00