Postgres Now Function

Introduction

When you’re using PostgreSQL to store your data, there are times when you’ll need to access the current date and time. PostgreSQL makes it easy to get this value with the NOW() function. In this article, we’ll show examples of how to use the PostgreSQL NOW function in different ways.

Prerequisites

For this tutorial, there’s only one important prerequisite that needs to be in place before proceeding: A PostgreSQL database cluster must be installed on your machine. To check whether it’s installed, use the command service PostgreSQL status in the psql command-line interface; once you get a response, type CTRL + C to exit.

Use psql to connect to a database

We’ll need to be in the psql command-line interface to try out the NOW() function. To access a PostgreSQL database with this interface, we’ll use the following command:

We’ll then use the command psql to manage the database and perform our SQL queries.

Postgres NOW() function

Once we’ve successfully entered the psql interface, we can use a SQL SELECT statement to return all date and time values that result from calling the NOW() function:

It will return a string representing the current time down to the exact millisecond. This string will look like the following:

The last two digits at the end (+00) represent the UTC (Coordinated Universal Time) time difference in hours—this value indicates your PostgreSQL time zone.

Postgres timestamp without time zone

We can append ::TIMESTAMP to the end of our SQL statement to omit the time zone from the response:

Set the PostgreSQL time zone

To change the Postgres time zone, use the SQL SET command as shown below:

If the time zone string provided for SET TIMEZONE is invalid, Postgres will default to the UTC (00) time zone.

PostgreSQL select current year

Let’s see how we can use the NOW() function to get the current year in PostgreSQL:

This command should return a date_part response that looks something like the following:

PostgreSQL Unix timestamp

In the following example, the EXTRACT() function makes use of the NOW() function to return an integer representing the current Unix epoch time:

It should return something like the following:

Create a PostgreSQL table with a timestamp column

Next, let’s create a table that will get the current date, time and time zone for a record in the table.

Here’s the SQL statement we’ll use to create our PostgreSQL table:

Now we’ll insert a value inside the table:

Once we have at least one record in our table, we can use the SELECT statement to query it:

Here’s another example that retrieves the date and time using the NOW function:

Set the ‘NOW’ interval

Our next example will demonstrate how to set an interval when using the NOW(); function within a transaction.

First we’ll BEGIN our transaction:

This notifies the SQL command console to begin the transaction.

To set the interval, we’ll use pg_sleep:

We’ll then select the time again for comparison purposes after the five-second sleep:

Finally, we’ll end the transaction by using COMMIT:

The result of setting an interval in a PostgreSQL NOW function using a transaction will look like the following:

NOTE: When you try to use the NOW() function in psql, you’ll get an interval of five seconds before it displays the result of the current time and date.

Another way to get the interval is to select the date and time operator using the NOW() function.

For example:

This is used to get the time that’s ten minutes ahead of the current time on your machine.

You can also use the - operator to get the time and date from a certain interval in the past:

Here’s an example:

The command shown above should return something like:

Conclusion

Being able to retrieve the current date and time is an essential skill when you’re working with data in PostgreSQL. In this article, we took a close look at the Postgres NOW function, and we learned how to use it in different ways to get the current time as well as times from a past or future interval. With our examples to use as a guide, you’ll be prepared to use the NOW() function in your own database commands.