Time in PostgreSQL: The simple way

Posted on 2013-08-06 by Hans-Jürgen Schönig

UPDATED August 2023: Time is one of those unloved topics every programmer has to deal with. Have you ever written a routine heavily dependent on time calculations in C, Python, PHP, Perl, or any other language? In that case, I’m sure you’ve fallen in love with time management as much as I have.

When it comes to processing time, PostgreSQL is really like a kind of revelation – simple, easy to deal with, efficient, and highly capable.

Day constants in PostgreSQL – YESTERDAY, TODAY, and TOMORROW

One of the most common tasks that PostgreSQL excels at is to figure out about yesterday, today and tomorrow. Consider the following query:

test=# SELECT 'YESTERDAY'::date, 'TODAY'::date, 'TOMORROW'::date;
    date    |    date    |    date
------------+------------+------------
 2013-08-05 | 2013-08-06 | 2013-08-07

(1 row)

These three PostgreSQL constants allow you to fetch important dates in SQL quickly and easily. There is no need for nasty math here.

PostgreSQL can also handle dates like February 29^th nicely:

SELECT DATE '2023-02-29';
ERROR:  date/time field value out of range: "2023-02-29"
LINE 1: SELECT DATE '2023-02-29';
                    ^

SELECT DATE '2024-02-29';

    date    
------------
 2024-02-29
(1 row)

Coping with intervals

But PostgreSQL is not only able to work with dates. It can also be extremely convenient in terms of working with intervals. Basically, an interval can be subtracted and added to a timestamp at will.

Consider the following example:

test=# SELECT now();
         now
-------------------------------
2013-08-06 10:41:19.202914+02
(1 row)

test=# SELECT now() + '3 decades 2 hours 5 centuries 20 minutes 90 days 12 months'::interval;
          ?column?
-------------------------------
2544-11-04 13:01:42.298739+01
(1 row)

The interval datatype accepts an easily readable format. You don’t even have to put the units in exact order – hours can be placed before centuries, or the other way around. There are absolutely no restrictions on order, which makes coding with this type a lot simpler.

Generating timeseries in PostgreSQL: `generate_series`

Did you ever write a web application which has to display a calendar? In case you did, I’m pretty sure you had a problem coming up with a list of days for a given month. In most programming languages, doing that kind of processing is pretty hard. PostgreSQL can assist here. It provides a simple, efficient method:

test=# SELECT * FROM generate_series('2012-02-26'::date, '2012-03-02'::date, '1 day'::interval);
    generate_series
------------------------
 2012-02-26 00:00:00+01
 2012-02-27 00:00:00+01
 2012-02-28 00:00:00+01
 2012-02-29 00:00:00+01
 2012-03-01 00:00:00+01
 2012-03-02 00:00:00+01
(6 rows)

The generate_series function takes three parameters: The first one defines the starting timestamp. The second parameter defines the ending timestamp, and the third parameter will tell PostgreSQL the size of the increments for the interval. In our example, we defined the interval length as one day– but you can use any increment you desire.

Keep in mind: Generating a timeseries can come in handy when you have to write outer joins – See the next blog post in this series for more info. Doing stuff like that on the database side can dramatically reduce the amount of engineering needed.

Update: `date_bin` and timestamps in PostgreSQL

Since this blog post was written, a new function was added in PostgreSQL 14 to solve a problem which has caused challenges for many users: How can we map timestamps to time bins? The function is called date_bin.

What people often do is round a timestamp to a full hour. That’s commonly done using the date_trunc function. But what if you want to round data in a way that things fit into a 30-minute or a 15-minute grid? Find out all about date_bin in this blog post.

Finally…

For more advanced information on how PostgreSQL processes time, see the following blogs:

Timeseries advanced analytics and window functions: EXCLUDE TIES, CURRENT ROW, and GROUP
Laurenz Albe’s blog post on Time Zone Management in PostgreSQL

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.