Time in PostgreSQL: The simple way

Time is one of those unloved topics every programmer has already dealt with. Have you ever written a routine heavily depending on time calculations in C, Python, PHP, Perl, or any other language? I am sure you have fallen in love with time management as much as I did.

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

TODAY, TOMORROW, and yesterday

One of the most common tasks PostgreSQL is very good at is figure out about today, tomorrow, and yesterday. 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)

Those three PostgreSQL constants allow you to fetch those important dates in SQL fast and easily. There is no need for nasty math here. PostgreSQL can handle dates as February 29^th and so on nicely.

Handling intervals

But PostgreSQL is not only able to work with simple dates. It can also be extremely convenient to work 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 easy and readable format. One of the nice things is that you don’t have to put in those units in exact order – hours can be placed before centuries or the other way round. There are absolutely no restrictions on order. This supposed to make handling this type a lot more simplistic.

Generating timeseries

Did you ever write a web application, which has to display a calendar? In case you did: I am pretty sure you had the problem of coming up with a list of days for a given month. In most programming languages doing this kind of processing is pretty hard. PostgreSQL can assist here and offers a simplistic and 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 start of the interval. The second parameter will tell the system when to end and the third parameter will tell PostgreSQL in which steps to increment the interval. In our example we have simply used one day but you can take any interval you desire.

Keep in mind: Generating a timeseries can come in handy when you have to write outer joins and so on. Doing stuff like that on the database side can dramatically reduce the amount of engineering needed.

———-
We welcome your feedback on our brand new Facebook page: www.fb.com/cybertec.postgresql