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 flamante 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:

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:

Run the following SELECT statement to read the current date:

Run the following SELECT statement to read the 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:

The following output will appear after executing the above query:

Conclusions:

The date and time data types are required to store date and time data in the PostgreSQL tables properly. The PostgreSQL database supports different formats of date, time, and timestamp data types to store date and time data in the tables. The uses of these date and time data types have been shown in this tutorial by creating a table that will help the new PostgreSQL users to create the table by using these data types.



Source link