PostgreSQL DateTime Data Types

Date/Time data types are used in PostgreSQL to store date and time data. The Julian date is used in PostgreSQL for different types of date and time calculations. The most commonly used January to December calendar use Julian date. Five different data types are used in PostgreSQL to store date and time data. These are date, time, timestamp, time with time zone, and interval. The size and purpose of these data types have been shown below.

date data type:

It is used to store calendar date values in the date field of the database table. It takes 4 bytes to store the data. Different date input formats and date output formats are supported by the PostgreSQL database table which is explained below.

Date Input Formats:

Some commonly used date input formats are given below:

Format	Description
Wednesday May 18, 2022	It uses the full weekday name, the full name of the month, the day of the month, and the year.
May 18, 2022	It uses the full name of the month, the day of the month, and the year.
2022-05-18	It uses standard ISO-8601 format that contains the numeric year, month, and day.
5/18/2022	It uses Non-European (U.S.) format that contains numeric month, day, and year.
18/5/2022	It uses a European format that contains numeric days, month, and year

Date Output Format:

The date output formats that are used in PostgreSQL are given below:

Format	Description
PostgreSQL	It uses the original PostgreSQL format. Example: Wed 18 May 11:30:00 2022 PST
SQL	It uses the traditional SQL format. Example: 05/18/2022 11:30:00.00 PST
ISO	It uses ISO-8601 standard format. Example: 2022-05-18 11:30:00-07
German	It uses the Regional Germany format. Example: 18.05.2022 09:30:15.00 PST

time data type:

Time and time with time zone values take 4 bytes to store the data in the table of the PostgreSQL. Some valid time formats are given below:

Format	Description
06:30	It uses ISO-8601 standard format.
07:20 AM	It is similar to 07:20.
07:20 PM	It is similar to 19:20.
15:15	It is similar to 03:15 PM.

Some valid time zone formats are given below:

Format	Description
03:45:20-5	It uses ISO-8601 standard format that prints 7 hours behind GMT.
03:45:20-05:30	It uses ISO-8601 standard format that prints 7 hours and 30 minutes behind GMT.
03:45:20-0530	It uses ISO-8601 standard format that prints 7 hours and 30 minutes behind GMT.

timestamp data type:

Timestamp with time zone value takes 8 bytes to store the data in the table of the PostgreSQL. Some valid timestamp formats are given below:

Format	Description
2022-05-25 12:25-7	It uses ISO-8601 date format with the minutes and PST time zone.
25/05/2022 12:30:20.100	It uses the European date format with microseconds.
05/25/2022 21:30	It uses the US date format with the minutes in 24-hour time.
25.05.2022 20:45:125 PM	It uses the German regional date format with the seconds, and PM.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root pemission:

$ sudo -u postgres psql

PostgreSQL of date and time examples:

Run the following SELECT statement to read the particular date:

# SELECT date('2022-05-25');

Run the following SELECT statement to read the current date and time:

# SELECT now();

Run the following SELECT statement to read the current date:

# SELECT current_date;

Run the following SELECT statement to read the current time:

# SELECT current_time;

The following output will appear after executing the above command:

Create a table using data and time data types:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create a table named ‘orders’ in the current database with five fields. The first field name is invoice_id, the data type is INT and it is the primary key of the table. The second field name is order_date and the data type is DATE. The third field name is order_time and the data type is TIME. The fourth field name is customer_id and the data type is VARCHAR(6). The fifth field name is entry_time and the data type is TIMESTAMP.

CREATE TABLE orders (
Invoice_id INT PRIMARY KEY,
order_date DATE,
order_time TIME,
customer_id VARCHAR (6),
entry_time TIMESTAMP);

The following output will appear if the table is created successfully:

Run the following insert query that will insert three records into the orders table. The order_date value has been given in ISO-8601 format. The order_time value has been given in ISO-8601 format. The current_timestamp value has been used in the entry_time field that will insert the timestamp value in ISO-8601 date format with the microseconds.

INSERT INTO orders
VALUES
(11785645, '2022-05-18', '10:30 AM', '785634', current_timestamp),
(11895634, '2022-05-17', '11:33 AM', '856345', current_timestamp),
(11128976, '2022-05-10', '08:30 PM', '906745', current_timestamp);

The following output will appear after executing the above query:

Run the following select query to read all records from the orders table:

SELECT * FROM orders;