How to search dates in PostgreSQL?

Question

I would like to compare a date entered in a search field (e.g., 04.07.2012) with a date from my table (a datetime column called date). However, I can't seem to get my SQL query straight.

What I tried:

find(:all, :conditions => ['\'to_timestamp(date, \'DD Mon YYYY\')\' LIKE \'to_timestamp(?, \'DD Mon YYYY\')\'', '#{query}'])

but it fails:

PG::Error: ERROR:  syntax error at or near "DD"
LINE 1: ...s".* FROM "projects"  WHERE ('to_timestamp(date, 'DD Mon YYY...
                                                             ^
: SELECT "pacients".* FROM "projects"  WHERE ('to_timestamp(date, 'DD Mon YYYY')' LIKE 'to_timestamp('#{query}', 'DD Mon YYYY')')

I'm a real novice as far as (postgre)SQL is concerned and I would really appreciate some hints in the right direction of writing this query.

Thanks a million!

Be sure to understand the difference between data types timestamp and date in PostgreSQL. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 4, 2012 at 18:10

Cody Caughlan · Accepted Answer · 2012-07-04 18:09:27Z

3

As meager pointed out there are a couple of issues here:

incorrect string interpolation
making the database do more work than necessary and using LIKE when you can do date searching more directly:

Let ActiveRecord format the date inputs use straight SQL to compare the dates, something like:

start_date = Date.civil(2012, 7, 4)
end_date = Date.civil(2012, 7, 20)
User.where(["date BETWEEN ? AND ?", start_date, end_date])

Converting the source date and the dates in the database to a timestamp formatted like a string and then using LIKE is very round-about and poor performing as well.

answered Jul 4, 2012 at 18:09

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mo5470 Over a year ago

Thank you, this is what I used in the end. In my project.rb model:

def self.search(query) 	begin 		if query 			Project.where(["date BETWEEN ? AND ?", Date.parse(query), Date.parse(query)]) 		else 			Project.all 	    end 	rescue ArgumentError  		Project.all 	end

Collectives™ on Stack Overflow

How to search dates in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related