How to Use PostgreSQL Date Functions

Introduction

If you’re using PostgreSQL to store and query your data, there will likely be times when you need to obtain the current date or the current time for your applications. You may even want to calculate a student or employee’s age. Fortunately, PostgreSQL offers a number of useful date and time functions to help you accomplish all of these tasks. In this article, we’ll take a closer look at PostgreSQL date functions and show you a few different ways you can use them.

Prerequisites

Before we get any deeper into a discussion of how to use PostgreSQL date functions, let’s take a moment to look at a few prerequisites that are required for this tutorial:

• First, you’ll need to ensure that PostgreSQL server is properly installed and configured, and that it’s running in the background.

If you’re using Windows or Linux, you can download PostgreSQL here.

• To start the PostgreSQL server on a Linux machine, use the following command:

• You can verify that the service is running using this command:

You should see output that looks like the following:

• If you’re using a Windows machine, the instructions for starting, stopping and restarting PostgreSQL server are shown below:

1. Open the Control Panel
2. Open Administrative Tools
3. Open Services
4. Within Services, locate the PostgreSQL Server service
5. Finally, stop, start or restart the service

PostgreSQL AGE Function

In many applications, there’s a need to calculate age. You may need to determine employee, student or client ages, or you may need to calculate years of tenure in an organization. Regardless of the particular circumstances, it’s easy to accomplish these tasks with the help of the PostgreSQL age() function.

We use the following syntax for the PostgreSQL age() function:

The function performs a subtraction using the values of the two arguments; the result is the difference between the two. The result is expressed in years, months and days, instead of days only.

Let’s look at an example of this function in actions:

There’s a variation on the syntax for the age() function that allows us to use the current date as the first timestamp argument.

For instance, let’s say we’d like to calculate the years of tenure of a certain employee. To do this, we can use the following syntax:

PostgreSQL CURRENT_DATE Function

The CURRENT_DATE function is one of the simplest of the PostgreSQL date functions since it doesn’t require any arguments when using it. The syntax is:

This function will return a DATE value that represents the present date. We can see how this works in the example below:

In the example shown above, we see that the output gives us a DATE value: “2019-09-04”.

PostgreSQL CURRENT_TIME Function

Another important PostgreSQL date function is CURRENT_TIME. The CURRENT_TIME function returns the present time within a particular time zone.

The syntax of this function is shown below.

We can see that this function takes an argument called precision. This optional argument will cause the result’s seconds field to be rounded to that number of fractional digits.

Let’s take a look at an example that doesn’t use the precision argument:

Note that the example shown above includes the result with no rounding.

The next example will show how to use the CURRENT_TIME function with precision set to 3:

PostgreSQL CURRENT_TIMESTAMP() Function

This PostgreSQL function returns the present date and time along with the time zone where the transaction was initiated.

The syntax of this function is shown below:

Note that this function also accepts a precision argument. If you omit this argument, the function will return a TIMESTAMP with a time zone where the value is not rounded at all.

Let’s look at an example that shows how to use the function to retrieve the current date and time:

As expected, the function returns the current date and time, and the returned value is not rounded at all since we did not include a precision argument.

Conclusion

Being able to retrieve the current date and time and calculating values such as age are essential skills for many applications. The PostgreSQL date functions make these tasks simple to accomplish. In this article, we looked at several PostgreSQL date functions and provided some real-life examples of their use. With the information provided in this tutorial, you’ll be ready to make use of these functions in your own applications. Jump to top