Better Date Manipulation in PostgreSQL Queries

Derrick Carr

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.

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.

FROM users
JOIN purchases ON user_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!