Date

In this section, we are going to understand the working of the PostgreSQL Date data type, examples of the Date data type, and some accessible date functions, which help us to handle Date values more efficiently.

What is PostgreSQL Date Data Type?

Another data type in PostgreSQL is DATE, which is used to store the date values.

In PostgreSQL, the Date data type format is YYYY-MM-DD, which helps us to store and insert the date records into the date column.

The Date data type involve 4 bytes of storage size. And the minimum and maximum ranges of date data type start with 4713 BC to 5874897 AD or 1000-01-01 to 9999-12-31.

We can use the CURRENT_DATE after the DEFAULT keyword if we want to use the current date as the column’s default value while creating a new table containing a Date column.

Syntax of PostgreSQL Date data type

PostgreSQL Date data type syntax is as follows:

variable_name DATE  

Example of PostgreSQL Date data type

Let us see one sample examples to understand how the PostgreSQL Date data type works.

We are creating one new table as Records with the CREATE command’s help and inserting some values using the INSERT command.

To create a Records into an Organization database, we use the CREATE command.

The Records table contains the various columns such as Records_id, Heading, and the Submission_date, and for the Submission_date column, we use the DATE data type and also accepts the current date as a default value.

CREATE TABLE Records (  

Records_id serial PRIMARY KEY,  

Heading VARCHAR NOT NULL,  

Submission _date DATE NOT NULL DEFAULT CURRENT_DATE  

);

Output

On executing the above command, we will get the following message, which displays that the Records table has been created successfully.

When the Records table is created successfully, we will insert some values into it with the INSERT command’s help.

 INSERT INTO Records (Heading)  

VALUES  

('Invoice to Client ABC'),  

('Invoice to Client PQR'),  

('Invoice to client XYZ'); 

Output

After implementing the above command, we will get the following message window, which displays that the specified values have been inserted successfully into the Records table.

After creating and inserting the Records table’s values, we will use the SELECT command to return all rows of the Records table:

SELECT *   

FROM Records;

Output

After successfully implementing the above command, we will get the below output, which displays all the data present in the Records table:

Note: According to the database server’s current date, we may get a diverse Submission_date value.

Examples for PostgreSQL Date functions

We have the following date functions such as NOW(), AGE(), EXTRACT(), TO_CHAR(), etc., available in the PostgreSQL, which help us to enhance the performance while we are using the Date data type.

Let us see different examples to understand how the PostgreSQL Date functions work.

First, we are creating new table as Student_details with the CREATE command’s help and inserting some values using the INSERT command.

We are going to create Student_details into an Organization database by using the CREATE command.

And the Student_details table contains the following columns Student_Id, Stu_first_name, Stu_last_name, Date_of_birth, and Admission_date columns, where the Date_of_birth and Admision_date contain the DATE data type.

 CREATE TABLE Student_details (  

    Student_Id serial PRIMARY KEY,  

    Stu_first_name VARCHAR,  

    Stu_last_name VARCHAR ,  

    Date_of_birth DATE NOT NULL,  

    Admission_date DATE NOT NULL  

); 

Output

On executing the above command, we will get the below message, which displays that the Student_details table has been created successfully into the Organization database.

Once the Student_details table has been generated, we will insert some values into it using the INSERT command.

 INSERT INTO Student_details(Stu_first_name, Stu_last_name,  

Date_of_birth, Admission_date)  

VALUES   

('Mike','Smith','1994-10-22','2018-08-11'),  

('William','Davis','1990-08-12','2018-08-10'),  

('Susan','Brown','1991-02-28','2018-07-10'),  

('Hannah','Beker','1995-12-13','2018-08-11'),  

('Margaret','Wilson','1994-04-25','2018-07-12')  

; 

Output

After executing the above command, we will get the below message window, which displays that the values has been inserted sucessfully into the Student_details table.

Once we are done creating and inserting the Student_details table’s, we will see the following Date data type functions:

Evaluate ages in Years, months, and days

We can use the AGE() function to analyze age at the present date in years, months, and days.

For example: In the below command, we use the AGE() function to compute students ages from the Student_details table.

SELECT Student_id, Stu_first_name,  

Stu_last_name, AGE(Date_of_birth)  

FROM Student_details;  

Output

After executing the above command, we will get the below output, which displays the ages for all the students with AGE() function.

Suppose we pass a date value to the AGE() function, it will deduct that date value from the existing date. And if we pass two parameters to the AGE() function, it will withdraw the second parameters from the initial parameter.

Let see one sample example to understand in detail:

In the below example, we will try to retrieve the age of students on 2010-10-08, as shown in the following command:

SELECT Student_id, Stu_first_name,  

Stu_last_name, AGE('2010-10-08',Date_of_birth)  

FROM Student_details; 

Output

After successfully executing the above command, we will get the below output, which displays all the student’s age on the specified date value.

Extract year, quarter, month, week, day from a date value

We can use the EXTRACT() function to retrieve the year, quarter, month, week, day from a date value,

For example: In the below command, we will extract the year, month, and day from the date of birth of students:

SELECT Student_id, Stu_first_name,  

Stu_last_name,   

EXTRACT (YEAR FROM Date_of_birth) AS YEAR,  

EXTRACT (MONTH FROM Date_of_birth) AS MONTH,  

EXTRACT (DAY FROM Date_of_birth) AS DAY  

FROM Student_details; 

Output

We will get the following result on implementing the above command, which displays the year, months, and days value separately.

Get the current date

We can use the built-in NOW() function to retrieve the current date and time. If we use the double colons (::) to cast a DATETIME value to a DATE value, we can retrieve only the date column records without the time part.

To return the current date of the database server, we can use the below command:

SELECT NOW()::date;  

Output

After implementing the above command, we will get the current date with the help of the NOW() function in the output, as shown below:

There is an Additional way to get the current date to use the CURRENT_DATE, as shown below:

SELECT CURRENT_DATE;  

Output

After executing the above statement, we will get the below output, which displays the Current date based on the database server.

Note: The output is in the following format: YYYY-MM-DD. Though, we can get the result of date value in various formats.

Get the interval between two dates

We can use the minus (-) operator to retrieve the interval between two dates.

Let us see one sample example for our better understanding:

The below command is used to get total days of Students by subtracting the values in the Admission_date column from current or today’s date:

 SELECT Stu_first_name, Stu_last_name,  

NOW() - Admission_date as Difference  

FROM Student_details; 

Output

After executing the above command, we will get the following output:

Getting an Output of a PostgreSQL date value in a particular format

We can use the TO_CHAR() function to get the Date value output in a specific format. And the TO_CHAR() function is used to take two parameters.

The first parameter is the value that we want to format, and another one is the template that specified the output format.

In the below example, we will show the current date in the following format: YYYY/MM/DD, with the help of the below command:

SELECT TO_CHAR(NOW() :: DATE, 'YYYY/MM/DD');  

Output

After successfully execution of the above command, we will get a similar date format, which we mention in the above statement:

Or we can use the below command if we want to see a date in the following format: SEP 29, 2020:

SELECT TO_CHAR(NOW() :: DATE, 'MON DD, YYYY');  

Output

After implementing the above command, we will get the below result, which displays the same format which we provide in the above statement:

Overview

In the PostgreSQL Date data type section, we have learned the following topics:

• The PostgreSQL Date data type is used to store the date values for a specified column.
• We used the different DATE functions, for example, NOW(), AGE(), EXTRACT(), and TO_CHAR() to enhance and handle the DATE value from the particular table.
• We used the CURRENT_DATE keyword to retrieve the current date for the particular column of the table.