---
title: Better Date Manipulation in PostgreSQL Queries
teaser: 'Use `interval` and `date_trunc` to make working with SQL dates easier.

  '
tags: postgresql,sql,data,web
author: Derrick Carr
published_on: 2017-09-19
---

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][postgres-docs] 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][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.

```sql
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][north-star] into a table, and to build custom
reporting views with [Scenic][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.

```sql
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`.

```sql
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][date-trunc-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!

[interval-data-type]: https://www.postgresql.org/docs/9.6/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
[north-star]: https://thoughtbot.com/blog/north-star-metric
[scenic]: https://thoughtbot.com/blog/announcing-scenic--versioned-database-views-for-rails
[postgres-docs]: https://www.postgresql.org/docs/9.6/static/functions-datetime.html
[date-trunc-list]: https://www.postgresql.org/docs/9.6/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC