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:

Date Output Format:

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

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:

Some valid time zone formats are given below:

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:

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:

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:

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:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-DateTime-Data-Types-1.png" data-lazy- height="534" src="data:image/svg xml,” width=”827″>

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

The following output will appear after creating the database:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-DateTime-Data-Types-2.png" data-lazy- height="104" src="data:image/svg xml,” width=”891″>

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.

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-DateTime-Data-Types-3.png" data-lazy- height="176" src="data:image/svg xml,” width=”821″>

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.

The following output will appear after executing the above query:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-DateTime-Data-Types-4.png" data-lazy- height="147" src="data:image/svg xml,” width=”850″>

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

The following output will appear after executing the above query:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-DateTime-Data-Types-5.png" data-lazy- height="189" src="data:image/svg xml,” width=”915″>

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.