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.

FUNCTIONRETURN TYPEDESCRIPTION
AGE(TIMESTAMP, TIMESTAMP)TIMESTAMPIt subtracts the two arguments and produces a “Symbolic” result represented using years, months, and days.
AGE(TIMESTAMP)TIMESTAMPIt subtracts the argument with the Current Date and returns the AGE in a “Symbolic” format.
CLOCK_TIMESTAMP()TIMESTAMP WITH TIMEZONEIt returns the Current Date and Time, the changes during the statement execution.
CURRENT_DATEDATEIt returns the Current Date.
CURRENT_TIMETIME WITH TIMEZONE It returns the Current Time.
CURRENT_TIMESTAMPTIMESTAMP WITH TIMEZONEIt returns the Current Date and Time, the start of the current transaction.
DATE_PART(TEXT, TIMESTAMP)DOUBLE PRECISIONIt returns the subfield of the TIMESTAMP in a Double Format.
DATE_PART(TEXT, INTERVAL)DOUBLE PRECISIONIt returns the subfield of the INTERVAL in a Double Format.
DATE_TRUNC(TEXT, TIMESTAMP)TIMESTAMPIt truncates the TIMESTAMP to the specified precision.
EXTRACT(FIELD FROM TIMESTAMP)DOUBLE PRECISIONIt returns the subfield of the TIMESTAMP in a Double Format.
EXTRACT(FIELD FROM TIMESTAMP)DOUBLE PRECISIONIt returns the subfield of the INTERVAL in a Double Format.
ISFINITE(DATE)BOOLEANIt checks whether the DATE is Finite or not.
ISFINITE(TIMESTAMP)BOOLEANIt checks whether the TIMESTAMP is Finite or not.
ISFINITE(INTERVAL)BOOLEANIt checks whether the INTERVAL is Finite or not.
JUSTIFY_DAYSINTERVALIt adjusts the INTERVAL in a way such that 30 days represent a month.
JUSTIFY_HOURSINTERVALIt adjusts the INTERVAL in a way such that 24 hours represent a day.
JUSTIFY_INTERVALINTERVALIt adjusts the INTERVAL using both JUSTIFY_DAYS and JUSTIFY_HOURS with some additional sign adjustments.
LOCALTIMETIMEIt returns the Current Time of the Day.
LOCALTIMESTAMPTIMESTAMPIt returns the Current Date and Time.
NOW()TIMESTAMP WITH TIMEZONEIt returns the Current Date and Time.
TIMEOFDAY()TEXTIt returns the Current Date and Time.
PostgreSQL DATE Functions

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
PostgreSQL DATE Function Examples
PostgreSQL- Connect to DataBase

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');
PostgreSQL DATE Function AGE()
PostgreSQL- DATE Function AGE()
  • AGE(TIMESTAMP)
SELECT AGE(TIMESTAMP'1976-09-15');
AGE() function in PostgreSQL DATE
PostgreSQL- DATE Function AGE()
  • CLOCK_TIMESTAMP()
SELECT CLOCK_TIMESTAMP();
PostgreSQL DATE Function CLOCK_TIMESTAMP()
PostgreSQL- DATE Function CLOCK_TIMESTAMP()
  • CURRENT_DATE
SELECT CURRENT_DATE;
How to get CURRENT_DATE in PostgreSQL using DATE Function
PostgreSQL- DATE Function CURRENT_DATE
  • CURRENT_TIME
SELECT CURRENT_TIME;
PostgreSQL DATE Function CURRENT_TIME
PostgreSQL- DATE Function CURRENT_TIME
  • CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP;
How to get CURRENT_TIMESTAMP in PostgreSQL using DATE Function
PostgreSQL- DATE Function CURRENT_TIMESTAMP
  • DATE_PART(TEXT, TIMESTAMP)
SELECT DATE_PART('HOUR', TIMESTAMP '1996-12-25 16:43:36');
PostgreSQL DATE Function DATE_PART
PostgreSQL- DATE Function DATE_PART()
  • DATE_PART(TEXT, INTERVAL)
SELECT DATE_PART('MONTH', INTERVAL '6 YEARS 7 MONTHS 13 DAYS');
PostgreSQL DATE Function DATE_PART
PostgreSQL- DATE Function DATE_PART()
  • DATE_TRUNC(TEXT, TIMESTAMP)
SELECT DATE_TRUNC('HOUR', TIMESTAMP '1996-12-25 16:43:36');
How to get DATE_TRUNC in PostgreSQL DATE Function
PostgreSQL- DATE Function DATE_TRUNC()
  • EXTRACT(FIELD FROM TIMESTAMP)
SELECT EXTRACT(HOUR FROM TIMESTAMP '1996-12-25 16:43:36');
PostgreSQL DATE Function EXTRACT
PostgreSQL- DATE Function EXTRACT()
  • EXTRACT(FIELD FROM INTERVAL)
SELECT EXTRACT(MONTH FROM INTERVAL '6 YEARS 7 MONTHS 13 DAYS');
PostgreSQL DATE Function EXTRACT
PostgreSQL- DATE Function EXTRACT()
  • ISFINITE(DATE)
SELECT ISFINITE(DATE '1996-12-25');
PostgreSQL DATE Function ISFINITE
PostgreSQL- DATE Function ISFINITE()
  • ISFINITE(TIMESTAMP)
SELECT ISFINITE(TIMESTAMP '1996-12-25 16:43:36');
How to use ISFINITE function in PostgreSQL
PostgreSQL- DATE Function ISFINITE()
  • ISFINITE(INTERVAL)
SELECT ISFINITE(INTERVAL '6 YEARS 7 MONTHS 13 DAYS');
PostgreSQL DATE Function ISFINITE
PostgreSQL- DATE Function ISFINITE()
  • JUSTIFY_DAYS(INTERVAL)
SELECT JUSTIFY_DAYS(INTERVAL '74 DAYS');
How to use JUSTIFY_DAYS in PostgreSQL
  • JUSTIFY_HOURS(INTERVAL)
SELECT JUSTIFY_HOURS(INTERVAL '36 HOURS');
JUSTIFY_HOURS in PostgreSQL
  • JUSTIFY_INTERVAL(INTERVAL)
SELECT JUSTIFY_INTERVAL(INTERVAL '2 MON - 2 HOUR');
How to use JUSTIFY_INTERVAL in PostgreSQL
  • LOCALTIME
SELECT LOCALTIME;
PostgreSQL DATE Function LOCALTIME
PostgreSQL- DATE Function LOCALTIME
  • LOCALTIMESTAMP
SELECT LOCALTIMESTAMP;
PostgreSQL DATE Function LOCALTIMESTAMP
PostgreSQL- DATE Function LOCALTIMESTAMP
  • NOW()
SELECT NOW();
PostgreSQL DATE Function NOW
PostgreSQL- DATE Function NOW()
  • TIMEOFDAY()
SELECT TIMEOFDAY();
PostgreSQL DATE Function TIMEOFDAY
PostgreSQL DATE Function 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);
PostgreSQL DATE Functions Add Days
PostgreSQL- Create Table DATE Format

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');
PostgreSQL INSERT DATA DATE
PostgreSQL- INSERT DATA DATE Format

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;
Add Days in PostgreSQL DATE Functions
PostgreSQL DATA Inserted Successfully DATE Format

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';
PostgreSQL UPDATE DATE
PostgreSQL- UPDATE DATE

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';
How to use PostgreSQL DATE Add Days function
PostgreSQL- UPDATE DATE Successful

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);
PostgreSQL DATE Functions Add Month
PostgreSQL- Create Table DATE Format

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');
PostgreSQL DATE Functions Add Month example
PostgreSQL- INSERT DATA DATE Format

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;
Add Month in PostgreSQL DATE Functions
PostgreSQL DATA Inserted Successfully DATE Format

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';
PostgreSQL UPDATE DATE Month
PostgreSQL- UPDATE DATE ADD Month

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';
PostgreSQL DATE Functions ADD Month
PostgreSQL- UPDATE DATE ADD Month Successful

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);
ostgreSQL DATE Functions Between
PostgreSQL- Create Table 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');
ostgreSQL DATE Functions Between example
PostgreSQL- DATE Function Insert Data

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;
PostgreSQL DATE Functions Between tutorial
PostgreSQL- DATA Inserted Successfully TIMESTAMP 24HOUR

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';
PostgreSQL DATE Function TIMESTAMP
PostgreSQL- DATE Function TIMESTAMP

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;
PostgreSQL DATE Function Last Day
PostgreSQL- DATE Function Last Day

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; 
PostgreSQL DATE Function Last Day
PostgreSQL- DATE Function Last Day

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');
PostgreSQL DATE Function Quarter
PostgreSQL- DATE Function QUARTER

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');
PostgreSQL- DATE Function QUARTER

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
PostgreSQL DATE Format Function
PostgreSQL- Connect to DataBase
  • TO_DATE(TEXT, TEXT)
SELECT TO_DATE('10 Jan 1997','DD Mon YYYY');
PostgreSQL DATE Function TO_DATE
PostgreSQL- DATE Function TO_DATE()

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

  • TO_TIMESTAMP(TEXT, TEXT)
SELECT TO_TIMESTAMP('10 Jan 1997','DD Mon YYYY');
PostgreSQL DATE Function TO_TIMESTAMP
PostgreSQL- DATE Function TO_TIMESTAMP

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);
PostgreSQL DATE Aggregate Functions
PostgreSQL- Create Table DATE Format

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');
PostgreSQL DATE Aggregate Functions example
PostgreSQL- INSERT DATA DATE Format

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;
DATE Aggregate Functions in PostgreSQL
PostgreSQL DATA Inserted Successfully DATE Format

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

SELECT COUNT(Date_Of_Joining)
FROM Employee;
PostgreSQL DATE Function COUNT
PostgreSQL- DATE Function COUNT()

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:

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