Sometimes we need functionality outside the reach of our ORM’s API. When that
happens we write good ol’ SQL. It’s a trade-off. We give up convenient date and
time helper methods (like
1.month.ago) in exchange for added query
flexibility. Fortunately Postgres provides a few tricks to
make working with dates smoother. Two that I’ve found most useful are the
interval data type and the
interval data type allows us to write our SQL
queries in ways that mirror how we’d speak them. On the web side, Rails gives
30.days.ago; on the database side, Postgres gives us
interval '30 days
Intervals can’t be used as timestamps, but they can be added to and subtracted from. Below, we subtract 30 days from the current time to return a list of users created in the past 30 days.
SELECT * FROM users WHERE created_at <= now() - interval '30 days'
A Practical Use Case
interval type shines when there are operations with relative times.
There’s a use for it with most any time-based reporting in an application. It’ll
be helpful long before you’re rolling your own analytics suite. I’ve seen it
used to pull a North Star metric into a table, and to build custom
reporting views with Scenic.
Let’s imagine we’re conducting product development research and want to interview users who purchased something through our e-commerce app within 90 days of sign-up. We can use interval to find the right users easily.
SELECT * FROM users JOIN purchases ON user_id = users.id WHERE purchases.created_at - users.created < interval '90 days';
The example is simple, but it shows that
interval can help you write cleaner
date- and time-based queries, even as the queries grow more complex.
date_trunc function allows us to specify the precision of the date. It
discards values below our target precision, so if we want to specify a timestamp
day, then all of the other fields down to
seconds are all zeroed out
in the return value.
The snippet below returns a collection of days when new records were created in
SELECT DISTINCT ON (created_on) date_trunc('day', created_at) AS created_on FROM arbitrary_table WHERE created_at > '2016-09-01' ORDER BY created_on DESC;
date_trunc function allows values that aren’t obvious parts of timestamps,
quarter. The full-list is available in the
date_trunc have helped me write more effective SQL queries
and answer thorny questions about app data. Go forth and write fearless