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:

psql someUserName -h 127.0.0.1 -d some_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:

CREATE TABLE employee(
id INT NOT NULL,
name TEXT,
join_date DATE DEFAULT CURRENT_DATE
);

This statement will return results that look like the following:

sample=# SELECT * FROM employee;
id | name | join_date
----+------------+------------
1 | Sierra BLu | 2019-10-04
(1 ROW)

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:

CREATE TABLE attendance(id serial PRIMARY KEY, Log_in TIME NOT NULL, Log_out TIME NOT NULL);

This statement will return output that looks like the following:

sample=# SELECT * FROM attendance;
id | log_in | log_out
----+----------+----------
1 | 08:00:00 | 17:00:00
(1 ROW)

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:

CREATE TABLE student(id serial PRIMARY KEY, name text, officially_enrolled TIMESTAMP);

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

sample=# SELECT * FROM student;
id | name | officially_enrolled
----+--------------+---------------------
1 | Brenda Lopez | 2015-06-15 00:00:00
(1 ROW)

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:

CREATE TABLE student(id serial PRIMARY KEY, name text, officially_enrolled TIMESTAMPTZ);

This statement will return the following output:

sample=# SELECT * FROM student;
id | name | officially_enrolled
----+--------------+------------------------
1 | Brenda Lopez | 2015-06-15 09:25:00+08
(1 ROW)oo

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:

sample=# SELECT now(), now() - INTERVAL '2 years 4 hours 30 minutes' AS "4 hours and 30 minutes and 2 years ago";

You should see output that looks like the following:

now | 4 hours AND 30 minutes AND 2 years ago
-------------------------------+----------------------------------------
2019-10-04 13:09:09.304279+08 | 2017-10-04 08:39:09.304279+08
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.