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 date_trunc function.
Using Interval
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
us 30.days.ago; on the database side, Postgres gives us interval '30 days
ago'.
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
The 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 Truncation
The 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
to the 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
arbitary_table.
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;
The date_trunc function allows values that aren’t obvious parts of timestamps,
like week and quarter. The full-list is available in the
Postgres docs.
Both interval and date_trunc have helped me write more effective SQL queries
and answer thorny questions about app data. Go forth and write fearless
(read-only) SQL!