・ Rails 6.0.3.2 ・ Mysql Ver 14.14 Distrib 5.6.47 ・ Osx10.15 ・ Deploy to heroku
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
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···)
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!
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