I have this table:

CREATE TABLE response(session_id int, seconds int, question_id int,
  response varchar(500),file bytea)

INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES 
(652,1459721866,31,0,NULL),(652,1459721866,32,1,NULL),(652,1459721866,33,0,NULL),
(652,1459721866,34,0,NULL),(652,1459721866,35,0,NULL),(652,1459721866,36,0,NULL),
(652,1459721866,37,0,NULL),(652,1459721866,38,0,NULL),(656,1460845066,31,0,NULL),
(656,1460845066,32,0,NULL),(656,1460845066,33,0,NULL),(656,1460845066,34,0,NULL),
(656,1460845066,35,1,NULL),(656,1460845066,36,0,NULL),(656,1460845066,37,0,NULL),
(656,1460845066,38,0,NULL),(657,1463782666,31,0,NULL),(657,1463782666,32,0,NULL),
(657,1463782666,33,0,NULL),(657,1463782666,34,0,NULL),(657,1463782666,35,1,NULL),
(657,1463782666,36,0,NULL),(657,1463782666,37,0,NULL),(657,1463782666,38,0,NULL)

seconds column is in unix epoch.

It is perfect to do:

SELECT * FROM
    crosstab ('select session_id, question_id, response from response order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

session_id  not_moving  foot    bike    motor   car bus metro   train   other
  652          0          1      0        0      0    0   0       0     null
  656          0          0      0        0      1    0   0       0     null
  657          0          0      0        0      1    0   0        0    null

But I want row that fall in April 2016 only (first two rows). So I tried:

SELECT * FROM
    crosstab ('select session_id, question_id, response from response order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500))
WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-30'

Error:

ERROR:  column "seconds" does not exist
LINE 12: WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-3...

I illustrate it in this dbfiddle.

EDIT

This didn't work:

SELECT * FROM
    crosstab ('select session_id, question_id, response from response WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-30' order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500))

ERROR:  syntax error at or near "2016"
LINE 2: ...rom response WHERE to_timestamp(seconds) BETWEEN '2016-4-1' ...
                                                             ^

CodePudding user response：

you can add the dates to the query, but you need to escape the single quotes.

SELECT * FROM
    crosstab ('select session_id, question_id, response 
                from  response WHERE to_timestamp(seconds) BETWEEN ''2016-04-01'' AND ''2016-04-30''
                order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500))

SELECT 2

fiddle