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 29th 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)
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:
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)
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.
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
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.
For more advanced information on how PostgreSQL processes time, see the following blogs:
- Timeseries advanced analytics and window functions:
CURRENT ROW, and
- Laurenz Albe’s blog post on Time Zone Management in PostgreSQL