PostgreSQL DATE Functions with Examples

In this PostgreSQL tutorial, we will learn about PostgreSQL DATE Functions. We use the DATE data type in PostgreSQL to store DATE type values in a table. We can use the PostgreSQL DATE Functions to compute various values such as the AGE() function to calculate age, CURRENT_DATE to display the current date, and many more.

• PostgreSQL DATE Functions
• PostgreSQL DATE Functions Examples
• PostgreSQL DATE Functions Add Days
• PostgreSQL DATE Functions Add Month
• PostgreSQL DATE Functions Last Day of Month
• PostgreSQL DATE Functions Quarter
• PostgreSQL DATE Functions Between
• PostgreSQL DATE Format Functions
• PostgreSQL DATE Aggregate Functions

PostgreSQL DATE Functions

In this section, we will learn about PostgreSQL DATE Functions. We will describe each function with its return type and a small description.

FUNCTION	RETURN TYPE	DESCRIPTION
AGE(TIMESTAMP, TIMESTAMP)	TIMESTAMP	It subtracts the two arguments and produces a “Symbolic” result represented using years, months, and days.
AGE(TIMESTAMP)	TIMESTAMP	It subtracts the argument with the Current Date and returns the AGE in a “Symbolic” format.
CLOCK_TIMESTAMP()	TIMESTAMP WITH TIMEZONE	It returns the Current Date and Time, the changes during the statement execution.
CURRENT_DATE	DATE	It returns the Current Date.
CURRENT_TIME	TIME WITH TIMEZONE	It returns the Current Time.
CURRENT_TIMESTAMP	TIMESTAMP WITH TIMEZONE	It returns the Current Date and Time, the start of the current transaction.
DATE_PART(TEXT, TIMESTAMP)	DOUBLE PRECISION	It returns the subfield of the TIMESTAMP in a Double Format.
DATE_PART(TEXT, INTERVAL)	DOUBLE PRECISION	It returns the subfield of the INTERVAL in a Double Format.
DATE_TRUNC(TEXT, TIMESTAMP)	TIMESTAMP	It truncates the TIMESTAMP to the specified precision.
EXTRACT(FIELD FROM TIMESTAMP)	DOUBLE PRECISION	It returns the subfield of the TIMESTAMP in a Double Format.
EXTRACT(FIELD FROM TIMESTAMP)	DOUBLE PRECISION	It returns the subfield of the INTERVAL in a Double Format.
ISFINITE(DATE)	BOOLEAN	It checks whether the DATE is Finite or not.
ISFINITE(TIMESTAMP)	BOOLEAN	It checks whether the TIMESTAMP is Finite or not.
ISFINITE(INTERVAL)	BOOLEAN	It checks whether the INTERVAL is Finite or not.
JUSTIFY_DAYS	INTERVAL	It adjusts the INTERVAL in a way such that 30 days represent a month.
JUSTIFY_HOURS	INTERVAL	It adjusts the INTERVAL in a way such that 24 hours represent a day.
JUSTIFY_INTERVAL	INTERVAL	It adjusts the INTERVAL using both JUSTIFY_DAYS and JUSTIFY_HOURS with some additional sign adjustments.
LOCALTIME	TIME	It returns the Current Time of the Day.
LOCALTIMESTAMP	TIMESTAMP	It returns the Current Date and Time.
NOW()	TIMESTAMP WITH TIMEZONE	It returns the Current Date and Time.
TIMEOFDAY()	TEXT	It returns the Current Date and Time.

In this section, we learned about PostgreSQL DATE Functions.

Read PostgreSQL ALTER TABLE 

PostgreSQL DATE Functions Examples

In this section, we will learn about PostgreSQL DATE Functions with some Examples. In the previous section, we learned about functions like AGE(), CURRENT_DATE, DATE_PART, etc., so in this section look at some examples for all these functions.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now we will see the implementation of all the functions mentioned in the last section:

• AGE(TIMESTAMP, TIMESTAMP)

SELECT AGE(TIMESTAMP'2018-07-18',TIMESTAMP'1988-12-23');

• AGE(TIMESTAMP)

SELECT AGE(TIMESTAMP'1976-09-15');

• CLOCK_TIMESTAMP()

SELECT CLOCK_TIMESTAMP();

• CURRENT_DATE

SELECT CURRENT_DATE;

• CURRENT_TIME

SELECT CURRENT_TIME;

• CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP;

• DATE_PART(TEXT, TIMESTAMP)

SELECT DATE_PART('HOUR', TIMESTAMP '1996-12-25 16:43:36');

• DATE_PART(TEXT, INTERVAL)

SELECT DATE_PART('MONTH', INTERVAL '6 YEARS 7 MONTHS 13 DAYS');

• DATE_TRUNC(TEXT, TIMESTAMP)

SELECT DATE_TRUNC('HOUR', TIMESTAMP '1996-12-25 16:43:36');

• EXTRACT(FIELD FROM TIMESTAMP)

SELECT EXTRACT(HOUR FROM TIMESTAMP '1996-12-25 16:43:36');

• EXTRACT(FIELD FROM INTERVAL)

SELECT EXTRACT(MONTH FROM INTERVAL '6 YEARS 7 MONTHS 13 DAYS');

• ISFINITE(DATE)

SELECT ISFINITE(DATE '1996-12-25');

• ISFINITE(TIMESTAMP)

SELECT ISFINITE(TIMESTAMP '1996-12-25 16:43:36');

• ISFINITE(INTERVAL)

SELECT ISFINITE(INTERVAL '6 YEARS 7 MONTHS 13 DAYS');

• JUSTIFY_DAYS(INTERVAL)

SELECT JUSTIFY_DAYS(INTERVAL '74 DAYS');

• JUSTIFY_HOURS(INTERVAL)

SELECT JUSTIFY_HOURS(INTERVAL '36 HOURS');

• JUSTIFY_INTERVAL(INTERVAL)

SELECT JUSTIFY_INTERVAL(INTERVAL '2 MON - 2 HOUR');

• LOCALTIME

SELECT LOCALTIME;

• LOCALTIMESTAMP

SELECT LOCALTIMESTAMP;

• NOW()

SELECT NOW();

• TIMEOFDAY()

SELECT TIMEOFDAY();

In this section, we learned about PostgreSQL DATE Functions with EXAMPLES.

Read PostgreSQL DATE Format + Examples

PostgreSQL DATE Functions Add Days

In this section, we will learn about ADD Days PostgreSQL DATE Functions. We can add days to a DATE column simply using the add operator, i.e., ” + ” operator. This can be used when we need to update the values in a column or can be used to display the date using the SELECT statement.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We INSERT the DATE inside single quotation marks as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now, we will update the Date_Of_Joining of Simona with Emp_ID = “2” using the UPDATE command.

UPDATE Employee
SET Date_Of_Joining = Date_Of_Joining + 5 * INTERVAL '1 DAY'
WHERE Emp_ID = '2';

We receive a confirmation message ” UPADTE 1 “, this means that the value has been updated successfully.

Now we can look at the updated value using the SELECT statement:

SELECT * 
FROM Employee
WHERE Emp_ID = '2';

In this example, we updated the Date_Of_Joining of Simona with Emp_ID = “2”. We updated it from “2018-08-12” to “2018-08-17”, i.e., we added 5 days to the Date_Of_Joining.

In this section, we learned about PostgreSQL DATE Functions- ADD Days.

Read PostgreSQL ADD COLUMN + 17 Examples

PostgreSQL DATE Functions Add Month

In this section, we will learn about PostgreSQL DATE Functions- ADD Month. We can add months to a DATE column simply using the add operator, i.e., ” + ” operator. This can be used when we need to update the values in a column or can be used to display the date using SELECT statement.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We INSERT the DATE inside single quotation marks as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now, we will update the Date_Of_Joining of Jose with Emp_ID = “10” using the UPDATE command.

UPDATE Employee
SET Date_Of_Joining = Date_Of_Joining + 2 * INTERVAL '1 MONTH'
WHERE Emp_ID = '10';

We receive a confirmation message ” UPDATE 1 “, which means that the value has been updated successfully.

Now we can look at the updated value using the SELECT statement:

SELECT * 
FROM Employee
WHERE Emp_ID = '10';

In this example, we updated the Date_Of_Joining of Jose with Emp_ID = “10”. We updated it from “2016-01-23” to “2016-03-23”, i.e., we added 2 months to the Date_Of_Joining.

In this section, we learned about PostgreSQL DATE Functions- ADD Month.

Read PostgreSQL WHERE IN with examples

PostgreSQL DATE Functions Between

In this section, we will learn about PostgreSQL DATE Functions BETWEEN. We can use the BETWEEN Operator with DATE data type and DATE functions as well. We will look at an example with DATE_TRNUC() DATE Functions and BETWEEN operator in the WHERE condition of SELECT statement of PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining TIMESTAMP);

Here we have created a column named Date_Of _Joining of TIMESTAMP data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD HH:MI format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25 13:30');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12 14:00');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21 14:30');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10 13:00');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16 12:30');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07 13:15');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13 13:45');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05 14:15');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24 14:45');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23 13:30');

We INSERT the TIMESTAMP inside single quotation marks in YYYY-MM-DD HH:MI format as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the BETWEEN operator and DATE_TRUNC() DATE function:

SELECT Emp_ID,Name,DATE_TRUNC('HOUR', Date_Of_Joining)
FROM Employee
WHERE Date_Of_Joining BETWEEN '2017-12-31' AND '2019-01-01';

In this example, we used the BETWEEN operator for TIMESTAMP type data and DATE_TRUNC() DATE function to truncate to the HOUR of the TIMESTAMP.

In this section, we learned about PostgreSQL DATE Functions BETWEEN.

Read PostgreSQL CASE with Examples

PostgreSQL DATE Functions Last Day of Month

In this section, we will learn about PostgreSQL DATE Functions Last Day of Month. We can compute the Last Day of any month using an expression in a SELECT statement:

SELECT (DATE_TRUNC('MONTH', '1998-01-14'::DATE) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::DATE
AS LAST_DAY_OF_MONTH;

In this example, we computed the last day of the “January” month of the year “1998”. Similarly, we can compute the Last day of any month in any year.

SELECT (DATE_TRUNC('MONTH', '2006-02-14'::DATE) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::DATE
AS LAST_DAY_OF_MONTH; 

In this example, we computed the Last day of the “February” month of the year “2006”.

In this section, we learned about PostgreSQL DATE Functions Last Day of Month.

Read PostgreSQL Export Table to CSV

PostgreSQL DATE Functions Quarter

In this section, we will learn about PostgreSQL DATE Function Quarter. Basically QUARTER is one of the field values of EXTRACT() DATE function which is used to compute the Quarter of the TIMESTAMP supplied.

The code to compute QUARTER using EXTRACT() function in PostgreSQL is:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '1996-12-08 16:40:30');

In this example, we computed the QUARTER for the TIMESTAMP ‘1996-12-08 16:40:30’, i.e., for the MONTH “December” which lies in the 4th QUARTER.

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2012-07-08 06:20:45');

In this example, we computed the QUARTER for the TIMESTAMP ‘2012-07-08 06:20:45’, i.e., for the MONTH “July” which lies in the 3rd QUARTER.

In this section, we learned about PotsgreSQL DATE Function QUARTER.

Read PostgreSQL TO_CHAR function

PostgreSQL DATE Format Functions

In this section, we will learn about PostgreSQL DATE Format Functions. We use two functions namely TO_DATE() and TO_TIMESTAMP() as PostgreSQL DATE Format Functions.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

• TO_DATE(TEXT, TEXT)

SELECT TO_DATE('10 Jan 1997','DD Mon YYYY');

The TO_DATE() function converts the TEXT argument into DATE type.

• TO_TIMESTAMP(TEXT, TEXT)

SELECT TO_TIMESTAMP('10 Jan 1997','DD Mon YYYY');

The TO_TIMESTAMP() function converts the TEXT argument into TIMESTAMP type.

In this section, we learned about PostgreSQL DATE Format Functions.

Read PostgreSQL TO_NUMBER() function

PostgreSQL DATE Aggregate Functions

In this section, we will learn about DATE Aggregate Functions in PostgreSQL. The aggregate functions in PostgreSQL are AVG(), COUNT(), MIN(), MAX(), and SUM().

We can use apply all these functions on DATE datatype. We will look at an example of COUNT() Aggregate function.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We INSERT the DATE inside single quotation marks as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the COUNT() function in PostgreSQL:

SELECT COUNT(Date_Of_Joining)
FROM Employee;

In this example, we computed the number of rows using the COUNT() function on the column Date_Of_Joining which is of the DATE type.

You may also like the following tutorials:

• PostgreSQL CREATE INDEX
• How to Restart PostgreSQL (Linux, Windows, Mac)
• PostgreSQL WHERE with examples
• Postgresql change column data type

In this tutorial, we learned, about PostgreSQL DATE Functions. Also, we have covered these topics:

• PostgreSQL DATE Functions
• PostgreSQL DATE Functions Examples
• PostgreSQL DATE Functions Add Days
• PostgreSQL DATE Functions Add Month
• PostgreSQL DATE Functions Between
• PostgreSQL DATE Functions Last Day of Month
• PostgreSQL DATE Functions Quarter
• PostgreSQL DATE Format Functions
• PostgreSQL DATE Aggregate Functions