PostgreSQL timestamp parameter: could not determine data type of parameter $1

Posted on December 30, 2015 at 7:11pm

I'm really struggling to get my query to work against my PostgreSQL database. Essentially, my query can be delimited by a timestamp, a parameter that the user can input, but which can also be empty. If no timestamp is provided, I must tell PostgreSQL to use '-infinity' instead. I tried writing the conditional statement below, but I get the error 'Could not determine data type of parameter $1' Can anyone tell me what's wrong with my query?

SELECT *
FROM log
WHERE CASE
  WHEN $P{timestampFrom} IS NULL THEN TIMESTAMP >= '-infinity'
  ELSE TIMESTAMP >= $P{timestampFrom}
  END

$P{timestampFrom} is a sql.Timestamp parameter.

Jaspersoft® Studio
jean.sebastien.lemay's picture
jean.sebastien.lemay
130
Joined: Dec 30 2015 - 7:02pm
Last seen: 6 years 9 months ago

1 Answer:

Posted on December 30, 2015 at 7:19pm

OK, I realised it was a PostgreSQL error. The solution is to force PostgreSQL to interpret the parameter as a timestamp

SELECT *
FROM log
WHERE CASE
  WHEN $P{timestampFrom}::TIMESTAMP IS NULL THEN TIMESTAMP >= '-infinity'
  ELSE TIMESTAMP >= $P{timestampFrom}
  END
jean.sebastien.lemay's picture
jean.sebastien.lemay
130
Joined: Dec 30 2015 - 7:02pm
Last seen: 6 years 9 months ago
Feedback
randomness