operator does not exist: timestamp without time zone ~~* unknown ruby code

Question

I am try to query users in the database when I do a search. However i am getting a specific error which is 'operator does not exist: timestamp without time zone ~~* unknown'

In my database, the record of data_of_birth is saving in the format below. Now sure what I am missing. Code below

field == "dob"
            selected_date = Date.strptime(query, '%Y-%m-%d')
            items = Patient.joins(role: :user).where('users.date_of_birth ILIKE :search', search: "%#{selected_date}%")

Here how date_of birth is saving in the DB

 date_of_birth: "1997-03-29 00:00:00"

Answer 1

Timestamps are stored as numbers in the database, not as strings, so using string comparisons (e.g. ILIKE) won't work.

It sounds like you're trying to match timestamps to dates. You can simply cast the timestamp to a date for that. Something like:

WHERE users.date_of_birth::date = '2019-06-14'

That would seem to translate to the following, based on your code.

field == "dob"
        selected_date = Date.strptime(query, '%Y-%m-%d')
        items = Patient.joins(role: :user).where('users.date_of_birth::date = :search', search: "%#{selected_date}%")

I'm unfamiliar with the libraries you're using, but it looks like the "::date" part might run into the placeholder functionality. You may need to tinker with that in order for it to work.