Report Sql Data source Postgres DB. Cannot run query with parameters

I believe the factor against me is how syntax in psql seems to want to work.

Working query in database query browser:
SELECT sum("InfluentMGD") as "Influent MGD" FROM hourlytotalization where t_stamp between '2022-7-14 09:00:00'::timestamp and '2022-7-15 00:00:00'::timestamp

Date formatting for datetime strings in reports.

my sql select query in the datasource for the report. That doesn’t run.

SELECT sum("InfluentMGD") as "Influent MGD" FROM hourlytotalization where t_stamp between '{StartDateString}'::timestamp and '{EndDateString}'::timestamp

error I am given. From the error it looks like it is having an issue casting the string date as a timestamp datatype.

WARN: An error occurred while executing Query SELECT sum(“InfluentMGD”) as “Influent MGD” FROM hourlytotalization where t_stamp between ‘{StartDateString}’::timestamp and ‘{EndDateString}’::timestamp – ERROR: invalid input syntax for type timestamp: “{StartDateString}” Position: 92

Directions from the manual that tell you how to reference string parameters.
my sql select query in the datasource for the report. That doesn’t run.

currently trying to right the entire sql query as a string parameter hoping I can drop it on the sql datasource…

** UPDATE

so I just wrote the query as a parameter string:
"SELECT sum(\"InfluentMGD\") as \"Influent MGD\" FROM hourlytotalization where t_stamp between '"+ dateFormat({StartDate},"yyyy-MM-dd HH:mm:ss")+"'::timestamp and '"+dateFormat({EndDate},"yyyy-MM-dd HH:mm:ss")+"'::timestamp ;"

and put it into a basic SQL Query like this
{TotalizationQuerry}

Tip: use the </> button to format your code properly. Select the code and press the button. (You can edit your post to fix it.) Split the SQL onto multiple lines for ease reading. Proper capitalisation, punctuation and complete sentences helps too.

Java timestamps (java.util.Date) have a standard string conversion that doesn’t match PostgreSQL’s requirements. If you cannot use proper parameters (question marks for substitution) then you must use an intermediate parameter to format the java timestamps in a PostgreSQL-friendly format.

Just say “no” to curly braces in SQL.

1 Like

really appreciate the response.

I see now some of my confusion was because I was improperly calling the parameters.

I had been referencing:

which I see now is not how to do it.