CODEWITHSUNDEEP
ruby-on-railspostgresqlsqlite
j-zhang
j-zhang

Reputation: 703

How to select from Postgresql's timestamp type by a special format with Rails?

For example, the created_at value is 2014-12-25 01:02:03, I want to get all the data which created_at like 2014-12 from database.

If SQLite, the code like this:

Model.where("created_at like ?", '2014-12' + "%")

But when PostgreSQL, how to do?

Addition

From the log, I found:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Blog Load (1.5ms)  SELECT  "blogs".* FROM "blogs" WHERE (created_at like '2014-12%')  ORDER BY created_at DESC LIMIT 30 OFFSET 0
Completed 500 Internal Server Error in 58ms
ActionView::Template::Error (PG::UndefinedFunction: ERROR:  operator does not exist: timestamp without time zone ~~ unknown

Upvotes: 0

Views: 224

Answers (1)

Roman Kiselenko
Roman Kiselenko

Reputation: 44370

In Postgres to get all data which created_at like 2014-12 you can use Postgres#date_part():

.where("date_part('year', created_at) = ? and date_part('month', created_at) = ?", '2014' , '12')

This query return all records where years created_at equal 2014 and month equal 12.

Upvotes: 1

Related Questions