Postgress Crosstab - Return and sql tuple descriptions are incompatible

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 wish the comeback was:

* 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

* Correction : I need to show time1, time2, time... of days 2016-01-01, 2016-01-02,...

Any idea what I might be doing wrong? Thank you.

Answer 1

The second column of the consultation provided to crosstab exists only for sorting and is not returned. In your case just extract the date from one of the columns timestamp returnees:

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 question review it seems that what you want is not Crosstab but rather 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