PostgreSQL Quick Tips: Working With Dates Using EXTRACT function
Imagine that you are implementing an e-commerce platform and want to grab all orders from the current year. What would be the simplest way of doing it in Rails? Probably writing a query looking like this:
Order.where("created_at >= ? AND created_at < ?", Date.today.beginning_of_year, Date.today.beginning_of_year.next_year)
It gets the job done but requires unnatural filtering by a range for a use case generic enough that it should be handled just using some native functions. Is it possible?
Apparently, it is! We can use
now() functions - the former could be used for extracting the current year from a timestamp and the latter could be used for getting the current time.
With those two functions, the query could look like the following one:
Order.where("EXTRACT(year FROM created_at) = EXTRACT(year FROM now())")
Much cleaner! And the great thing is that you can also create a functional index for
EXTRACT(year FROM created_at) to avoid sequential scanning and get much better performance.