Jump to content

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


Go to solution Solved by jean.sebastien.lemay,

Recommended Posts

Posted

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 logWHERE CASE  WHEN $P{timestampFrom} IS NULL THEN timestamp >= '-infinity'  ELSE timestamp >= $P{timestampFrom}  END[/code]

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

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

  • Solution
Posted

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

SELECT *FROM logWHERE CASE  WHEN $P{timestampFrom}::timestamp IS NULL THEN TIMESTAMP >= '-infinity'  ELSE TIMESTAMP >= $P{timestampFrom}  END[/code]

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...