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

user_11530585 0
Viewed 23457

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"
postgresql ruby-on-rails ruby sql
1 Answers

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.

user_62174900
Related