[RUBY] operator does not exist: handling the error timestamp without time zone ~~ unknown

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 app with rails and deploying it to heroku.

When I check the error on heroku log, I find the following display

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

I didn't understand the error statement at first, but since timestamp is a column type, I found out that something was wrong with the type and looked it up further.

Then, it turned out that there was a problem with the conditional statement of the DB search. In a little more detail, a problem occurred because the logic that was searching by the data creation date and time was searching by a character string (string type) as shown below.

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

Cause

The cause is that heroku's DB is "PostgreSQL", which is different from my environment (MySQL). In "MySQL", it seems that 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 a method called xxx.in_time_zone. For example, in my case, if I want to collect data on a monthly basis 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 search data with timestamp type, so it worked well with "PostgreSQL"! Also, the description has decreased and it is refreshing! You can change the last all_month to all_day etc. and it's quite usable! There is a link for details, so it is written in a very easy-to-understand manner!

reference

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

Recommended Posts

operator does not exist: handling the error timestamp without time zone ~~ unknown
[Error] ActiveRecord :: NoDatabaseError FATAL: database does not exist
About the "Oops VFS connection does not exist" error and solution in AWS Cloud9
After introducing Better_errors, the error screen does not change
About error PG :: UndefinedTable: ERROR: relation "XXXXXX" does not exist
"Error: Package xxx does not exist" "Error: Symbol not found" when rewriting kotlin
Handling when calling a key that does not exist in hash