I have a problem. In my application I have a list with data. This data includes a datetime_utc column. There is also a searchbar which allows the user to search for datetime strings, but the searchbar is now requiring precise inputs, so for example these inputs return a result:
2023-01-01 02:00:00
2023-01-01 02:00
2023-01-01 02
As long as the input string starts with a valid datetime format. What I want is for example to look for only a given time like:
02:00
This should return all data with the time 02:00:00 as datetime_utc value.
Right now this is my code:
data = data.where(datetime_utc: "#{params[:search]}") if params[:search] && params[:search] != ''
And I have tried things like this:
data = data.where("to_char(datetime_utc, 'YYYY-MM-DD HH24:MI:SS') LIKE ?": "%#{params[:search]}%") if params[:search] && params[:search] != ''
But this gives me the error:
PG::UndefinedColumn: ERROR: column candlesticks.to_char(datetime_utc, 'YYYY-MM-DD HH24:MI:SS') LIKE ? does not exist
So I understand that Postgres formats the given input string to a datetime format first and I it can’t do that, it crashes.
There must be a way to still get this to work right?
>Solution :
Rails is considering "to_char(datetime_utc, 'YYYY-MM-DD HH24:MI:SS') LIKE ?" as column name because of the incorrect format used
You need to change your query to (Notice the : is replaced with ,)
data = data.where("to_char(datetime_utc, 'YYYY-MM-DD HH24:MI:SS') LIKE ?", "%#{params[:search]}%") if params[:search] && params[:search] != ''