function date_trunc("unknown", "unknown") is not unique - SQL

Recently I started working on Japser Studio professional for my new project Cloud to generate the reports. I was very new to all cloud technologies including PostgreSql. I faced lot of problems during this time. below is one of such problem, which kill lot of my time. Here is problem and its solution.

Problem statement: Need to generate usage data reports.

To generate usage data reports, I need to compare the existing timestamp from the table with user given data. For comparing data I used date_trunc()  to get year/month/day etc. Here I was getting above error. Below is the query for that where ts is from test_reports table.

select * from test_reports where (date_trunc('day',ts)


ERROR: function date_trunc("unknown", "unknown") is not unique
  Hint: Could not choose a best candidate function. You may need to add explicit type casts. [SQL State=42725]

This error is due to data type(timestamp) mismatching. By seeing/analysing the error, I found its due to mismatching the time. But no idea how to solve it and googled for it like hell. Luckily our TL has suggested the solution that prepending timestamp to the user input variable.

select * from test_reports where (date_trunc('day',ts)timestamp

 '2014-03-21 14:07:00'))
Jasper studio professional:

Need to create the input parameter and pass that parameter as an argument to the sql query. Here are the steps to create the parameter.

1. Go to outline window in the Jasper studio
2. Right click on the Parameters tab and click on "Create Parameter"
3. Then click on the created parameter and go to the properties window
4. Change the name to appropriate one
5. Change the class name to java.sql.timestamp
6. Change the default expression to new Date() as it will give the current date as default value
7. Make the isPrompt check box enable for getting the data from user

Now we can able to read the date value from the user into this newly created parameter.

Usage of the parameter in generating reports:

Need to follow below syntax for  using parameter in the reports generation.

$P!{parameter_name}

select * from test_reports where ts < '$P!{user_date}'

If we use same parameter in date_trunc() function, we will face type mismatch error.

select * from test_report where date_trunc('day',update_ts)

To solve that error, you can prepend 'timestamp' which will convert to required type.

select * from test_report where date_trunc('day',update_ts)timestamp
'$P!{user_date}')

Thanks for reading !!!!