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 isactive
. 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 havepsql
installed , typepsql -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:
1 | 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 dateTIME
– only stores the time of the dayTIMESTAMP
– stores both time and dayTIMESTAMPTZ
– stores both time and day with a time zoneINTERVAL
– 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 | 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:
1 2 3 4 5 | 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:
1 | CREATE TABLE student(id serial PRIMARY KEY, name text, officially_enrolled TIMESTAMP); |
The statement shown above should return a result that looks like this:
1 2 3 4 5 | 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:
1 | CREATE TABLE student(id serial PRIMARY KEY, name text, officially_enrolled TIMESTAMPTZ); |
This statement will return the following output:
1 2 3 4 5 | 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:
1 | 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:
1 2 3 4 | 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