# Working with time in Postgres

DevFeed: [Working with time in Postgres](<https://devfeed.tech/articles/working-with-time-in-postgres-41196.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2017/06/08/working-with-time-in-postgres/>)

Author: Map

Published: 2017-06-08T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Database](<https://devfeed.tech/topics/database.md>), [Time Series](<https://devfeed.tech/topics/time-series.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Data analysis](<https://devfeed.tech/topics/data-analysis.md>), [functions](<https://devfeed.tech/topics/functions.md>)

Tags: [data](<https://devfeed.tech/tags/data.md>), [data-analysis](<https://devfeed.tech/tags/data-analysis.md>), [database](<https://devfeed.tech/tags/database.md>), [functions](<https://devfeed.tech/tags/functions.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [time-series](<https://devfeed.tech/tags/time-series.md>)

## AI overview

This tutorial explains how to work with time in Postgres, covering interval-based date arithmetic, built-in date functions such as date_trunc, weekly aggregation, and generating missing time ranges for reports.

## Source excerpt

A massive amount of reporting queries, whether really intensive data analysis, or just basic insights into your business involving looking at data over a certain time period. Postgres has really rich support for dealing with time out of the box, something that's often very underweighted when dealing with a database. Sure, if you have a time-series database it's implied, but even then how flexible and friendly is it from a query perspective? With Postgres there's a lot of key items available to you, let's dig in at the things that make your life easier when querying. Date math The most common thing I find myself doing is looking at users that have done something within some specific time window. If I'm executing this all from my app I can easily inject specific dates, but Postgres makes this really easy for you. Within Postgres you have a type called an interval that is some window of time. And fortunately Postgres takes care of the heavy lifting of how might something translate to or from hours/seconds/milliseconds/etc. Here's just a few examples of things you could do with interals: '1 day'::interval '5 days'::interval '1 week'::interval '30 days'::interval '1 month'::interval A note that if you're looking to remove something like a full month, you actually want to use 1 month instead of trying to calculate yourself. With a given interval you can easily shift some window of time, such as finding all users that have signed up for your service within the past week: SELECT * FROM users WHERE created_at >= now() - '1 week'::interval Date functions Date math makes it pretty easy for you to go and find some specific set of data that applies, but what do you do when you want a broader report around time? There's a few options here. One is to leverage the built-in Postgres functions that help you work with dates and times. date_trunc is one of the most used ones that will truncate a date down to some interval level. Here you can use the same general values as the above, bu