CYBERTEC PostgreSQL Logo

Time in PostgreSQL: The simple way

08.2013 / Category: / Tags:

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:

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:

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:

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:

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:


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

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram