Temporal Data in PostgreSQL

Introduction to temporal data in PostgreSQL

When you’re using a database to store your organization’s data, it’s common to have date and time information in your records. In PostgreSQL, there are a few different temporal data types that can be used to store date and time information. Temporal data in PostgreSQL is any kind of data type that relates to dates and time. In this article, we’ll provide an overview of these temporal data types and show some examples of how they’re used.

Prerequisites

Before we can look at any examples of temporal data types in PostgreSQL, there are a few prerequisites that are essential for this task:

• First, you need to make sure that PostgreSQL is installed. If you’re not sure, use the service postgresql status command to check if the status is active. You can then press CTRL + C to exit.

• You’ll also need to have psql, the interactive PostgreSQL command-line interface, installed on your machine. To see if you have psql installed , type psql -V— this command will also indicate the version number.

Go into the ‘psql’ command-line interface for PostgreSQL

Let’s go into the psql interface so that we can execute some SQL commands. Use the following command in a terminal window to access a PostgreSQL database:

You’ll be prompted to supply the user’s password. After you input the password and press Return, you’ll have access to the database.

Temporal data types in PostgreSQL

There are several temporal data types available in PostgreSQL:

• DATE – only stores the date
• TIME – only stores the time of the day
• TIMESTAMP – stores both time and day
• TIMESTAMPTZ – stores both time and day with a time zone
• INTERVAL – stores period of time

PostgreSQL ‘DATE’ data type

When we store a DATE value in PostgreSQL, the database uses a yyyy-mm-dd format.

Here’s an example of a DATE data type that uses the CURRENT_DATE() function:

This statement will return results that look like the following:

NOTE: You can also add the DEFAULT CURRENT_DATE constraint after the date value so that the default value will be the current date on your machine.

PostgreSQL ‘TIME’ data type

When PostgreSQL uses the TIME value, it declares and stores the time of day. The common format for time values in PostgreSQL is HH:MM:SS. We can see an example below:

This statement will return output that looks like the following:

PostgreSQL ‘TIMESTAMP’ data type

When the TIMESTAMP value is used in PostgreSQL, it shows both the stored date and time value. Let’s look at an example of the timestamp value in action:

The statement shown above should return a result that looks like this:

PostgreSQL ‘TIMESTAMPTZ’ data type

The PostgreSQL TIMESTAMPTZ data type is a version of TIMESTAMP that provides and shows the current timezone in the column. Here’s an example of a CREATE TABLE statement that makes use of the TIMESTAMPTZ data type:

This statement will return the following output:

NOTE: You can see that the timezone is represented as +08 in the table.

PostgreSQL ‘INTERVAL’ data type

The INTERVAL data type stores and manipulates a period of time. This data type can describe a period of time in a variety of formats, including years, months, days, hours, minutes and seconds. Let’s look at an example that uses the INTERVAL data type:

You should see output that looks like the following:

NOTE: The PostgreSQL interval data type is particularly useful when doing date and time arithmetic.

Conclusion

When you’re working with temporal data– data related to date and time– in PostgreSQL, it’s important to use the right data type for your needs. PostgreSQL provides a wide variety of temporal data types to handle all kinds of date and time information. In this article, we reviewed examples using all the temporal data types available in PostgreSQL. With these examples to guide you, you’ll be able to make good use of temporal data types in your own PostgreSQL applications.