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.
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.