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.
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 statuscommand 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
psqlinstalled , 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
Here’s an example of a
DATE data type that uses the
id INT NOT NULL,
join_date DATE DEFAULT CURRENT_DATE
This statement will return results that look like the following:
id | name | join_date
1 | Sierra BLu | 2019-10-04
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:
id | log_in | log_out
1 | 08:00:00 | 17:00:00
PostgreSQL ‘TIMESTAMP’ data type
TIMESTAMP value is used in PostgreSQL, it shows both the stored
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:
id | name | officially_enrolled
1 | Brenda Lopez | 2015-06-15 00:00:00
PostgreSQL ‘TIMESTAMPTZ’ data type
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:
id | name | officially_enrolled
1 | Brenda Lopez | 2015-06-15 09:25:00+08
NOTE: You can see that the
timezone is represented as
+08 in the table.
PostgreSQL ‘INTERVAL’ data type
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:
2019-10-04 13:09:09.304279+08 | 2017-10-04 08:39:09.304279+08
NOTE: The PostgreSQL
interval data type is particularly useful when doing date and time arithmetic.
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