[Ruby] Dealing with the error operator does not exist: timestamp without time zone ~~ unknown

1 minute read

environment

・Rails 6.0.3.2 ・Mysql Ver 14.14 Distrib 5.6.47 ・OSX10.15 ・Deploy to heroku

Synopsis

An error occurred when creating a web application with rails and deploying it to heroku.

When the error is confirmed with heroku log, the following display is found

operator does not exist: timestamp without time zone ~~ unknown

I didn’t quite understand the error statement at first, but since timestamp is a column type, I found that something was wrong with the type, and I investigated further.

Then, it turned out that there was a problem with the DB search condition statement. To be a little more specific, a problem occurred because the logic that was searching by the date and time of data creation was searching with a character string (string type) as follows.

where('created_at: LIKE(?)', "%%-%%-%%") # The code on the left is the problem code

Cause

The cause is that heroku’s DB is “PostgreSQL” and it is different from my environment (MySQL). In “MySQL”, even if you search for a timestamp type column with a string type, it will be converted automatically (tolerance). It seems that “PostgreSQL” does not work unless the SQL statement is strict. (Strict···)

Countermeasures

I solved it by using the method xxx.in_time_zone.! For example, if you want to collect monthly data for yourself and want to acquire data for August 2020 (below)

search_time = "2020-08-01"
(Model name).where(created_at: search_time.in_time_zone.all_month)

By doing so, you can successfully search for data with the timestamp type, so it worked well with “PostgreSQL”! Also, the description is reduced and it is refreshing! You can change the last all_month to all_day etc. and you can use it quite a bit! For details, there is a link so that it is very easy to understand!

reference

About error statements ・Https://nobuneko.com/blog/archives/2010/05/postgresql83operator_does_not.html About Time class and DateTime class ・Https://qiita.com/jnchito/items/cae89ee43c30f5d6fa2c