Bind datepicker to postresql fails

[appsmith_user]

Situation:

• page with datepicker
• updatequery with … “created_at” = ‘{{update_col_3.selectedDate}}’ …,
• use PreparedStatements = TRUE
• postgres from supabase

Problem:

• update query fails with status 5005
  → error: “ERROR: column “created_at” is of type timestamp with time zone but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 70”
  → substituted params $2: {“value”:“2022-02-07T07:00:00.000Z”, “type”:“STRING”}

What I tried (amongst many others):
1- replacing the dynamic form binding with the substituted param value from the error response: “created_at” = ‘2022-02-07T07:00:00.000Z’, → THIS WORKS, though has no binding to user input
2. disabling use prepared statement with the binding: “created_at” = ‘{{update_col_3.selectedDate}}’, → THIS WORKS, though now I’m susceptive to SQL injection,…

Question: how to use the datepicker field binding.selecteddate with preparedStatement=TRUE?

Thanks!

[RohanArthur]

Hi there!

Please try this, it should work:
{{moment(update_col_3.selectedDate).format(‘X, z’)}}

I’m assuming the timezone and timestamp format here with the ‘X, z’ argument, but you can find the suitable one here: Format - momentjs.com

Please let us know how this goes.

Thanks,
Rohan

[appsmith_user]

@RohanArthur

.format(‘X, z’)

Thanks for your reply, this unfortunately seems not to work:

1."applicationDate" = {{moment(updateApplicationDate.selectedDate).format('X, z')}}, results in the error:

Query preparation failed while inserting value: 1645474887, for binding: {{moment(updateApplicationDate.selectedDate).format(‘X, z’)}}. Please check the query again. Error: For input string: "1645474887, "

note: with “use prepared statement disabled” and/or encapsolating it in ’ — ’ does unfortunately not work as well

I try to understand what happens in your proposal:

• .format(‘X, z’) creates a string output that has a unix timestamp (0-9 digits) followed by a a ‘,’ and then the timezone. I think this is not an ISO8601 format (assuming that it has to be)?

Please let me know what I could be doing/ interpreting wrong. thanks!

[RohanArthur]

Thank you for sharing all this context, and i know it must be frustrating trying to get this to work. Would you please let me know what is the data type of your date column in the database? I’ll then try to replicate this situation on my own.
PS: please also share which db you’re using (postgres, MySQL etc)

[appsmith_user]

Thanks Rohan, my situation:

• supabase postgresql
• timestamptz

thanks!

[RohanArthur]

Thank you for your patience!
I got it to work with: {{moment(DatePicker1.selectedDate).format('YYYY-MM-DD HH:mm:ss')}}

Screenshots:

[appsmith_user]

Thanks, your solution works!