PostgreSQL's DATE data type allows you to store and manipulate date values in your database. You can easily track events, deadlines, and other important dates by storing dates in your database. Apart from storing dates, Postgres offers various operators and functions. These functions and operators enable us to perform different operations on dates, such as finding intervals between two dates or extracting a specific part from a timestamp.

Content Overview

• What is Date Data Type, and How to Use it in Postgres?
• Built-in Date Functions in Postgres
• How to Format a Date in Postgres?
• How to Get an Interval Between Two Dates in Postgres?

So, let’s begin!

What is DATE Data Type, and How to Use it in Postgres?

In Postgres, the DATE data type is used to store the date values in a table(by default, it stores the dates in “YYYY-MM-DD” format). The syntax of the DATE data type is as simple as any other built-in data type, such as TEXT, INT, NUMERIC, etc.

CREATE TABLE tbl_name(
column_name DATE
);

The above syntax creates a date-type column in any particular table "tbl_name".

Example: How Does the DATE Data Type Work in Postgres?

Firstly, let’s create a table with a date-type column:

CREATE TABLE published_articles(
article_id SERIAL PRIMARY KEY, 
article_name TEXT,
published_date DATE
);

A table named “published_artices” has been created. Let’s learn how to insert the data into a Postgres table via the INSERT INTO command:

INSERT INTO published_articles(article_name, published_date)
VALUES ('IF Statement in Postgres', '2022-07-01'),
('How to Count Unique Values in Postgres', '2022-08-28'),
('How to Delete Duplicates in Postgres', '2022-09-15'),
('AVG() Function in PostgreSQL', '2022-07-10'),
('Delete Table PostgreSQL', '2022-07-01');

To verify the data insertion, you can execute the “SELECT *” command as follows:

SELECT * FROM published articles;

This is how you can create a date-type column, insert data into it, or fetch data from it.

Built-in Date Functions in Postgres

Postgres offers multiple built-in functions to work with dates. In this Postgres blog, we are going to discuss the most frequently used date functions via Practical examples:

1) Postgres CURRENT_DATE Function

The below-mentioned points illustrate the working of the Postgres CURRENT_DATE function:

• In PostgreSQL, the CURRENT_DATE function is used to get the current/today’s date and time.
• Return type: “DATE”.
• It retrieves the date in “YYYY-MM-DD” format.

Basic syntax

CURRENT_DATE;

The CURRENT_DATE function doesn’t accept any argument.

Example: How to Use CURRENT_DATE in Postgres?

This example shows that the CURRENT_DATE function retrieves the current date in “YYYY-MM-DD” format:

SELECT CURRENT_DATE;

2) Postgres NOW() Function Function

Consider the following points to comprehend the working of the NOW() function:

• Postgres offers an inbuilt date function named NOW() that retrieves the current date, time, and timezone.
• Return type: TIMESTAMPTZ.
• It retrieves the date in the “YYYY-MM-DD HH-MM-SS.MS-TZ” format.

Basic Syntax

NOW();

The NOW() function doesn’t require any argument.

Example: How to Use NOW() Function in Postgres?

This example uses the Postgres’ NOW() function to retrieve the current date, time, and time zone based on the system’s date:

SELECT NOW();

3) Postgres CURRENT_TIMESTAMP Function

The below-listed points elaborate on the working of the CURRENT_TIMESTAMP function:

• The CURRENT_TIMESTAMP function in Postgres retrieves the current date, time, and timezone at which a transaction begins.
• Return Type: TIMESTAMPTZ.
• It retrieves the date/time in the “YYYY-MM-DD HH-MM-SS.MS-TZ” format.

Basic Syntax

CURRENT_TIMESTAMP;

The syntax shows that the CURRENT_STAMP function will be used without parentheses and arguments.

Example: How to Use CURRENT_TIMESTAMP in Postgres?

Executing the below line of code will show you that the targeted function retrieves the timestamp with the time zone:

SELECT CURRENT_TIMESTAMP;

4) Postgres LOCALTIMESTAMP Function

The below-listed points will help you understand the working of the LOCALTIMESTAMP function:

• Another widely used built-in function is LOCALTIMESTAMP, which retrieves the time and date at which the current transaction began.
• Return Type: TIMESTAMP.
• It retrieves the date and time in the “YYYY-MM-DD HH-MM-SS.MS” format.

Basic Syntax

LOCALTIMESTAMP;

The syntax shows that the LOCALTIMESTAMP function doesn’t accept any argument and will be used without parentheses.

Example: How to Use the LOCALTIMESTAMP Function in Postgres?

Run the following line of code to get the current date and time without the time zone:

SELECT LOCALTIMESTAMP;

5) Postgres DATE_PART() Function

DATE_PART() is a built-in function in Postgres that is used to get only a specific part/field of timestamp, date, interval, etc.

• Return Type: DOUBLE PRECISION.
• It accepts a field and a source as arguments.
• The value for the “field” argument must be valid.

Basic Syntax

DATE_PART(field, source);

The above snippet shows that the DATE_PART() function accepts two arguments: a field and a source. The DATE_PART() function will extract/pull out the field from the specified source.

Example: How to Use DATE_PART() Function in Postgres?

Suppose we want to pull out a year from a specific date; for this purpose, we will use the DATE_PART() function as follows:

SELECT DATE_PART('Year', TIMESTAMP '2022-12-09');

6) Postgres DATE_TRUNC() Function

In PostgreSQL, DATE_TRUNC() is a built-in date function that truncates/trims the unnecessary part from the date/time.

• Return Type: TIMESTAMP.
• It accepts a “datePart” and a “field” as arguments.
• The value for the “field” argument must be valid.
• It retrieves the trimmed part with a specific precision level.
• Using the DATE_TRUNC() function, all values of the given field except the specified date will be reset to their initials.

Basic Syntax

DATE_TRUNC(datePart, field);

The datePart must be valid, as described in the “DATE_TRUNC() Function in Postgres” blog. While in place of a field argument, you can specify an INTERVAL or TIMESTAMP.

Example: How to Use DATE_TRUNC() Function in Postgres?

Let’s learn how the DATE_TRUNC() function work in Postgres:

SELECT DATE_TRUNC('Year', TIMESTAMP '2022-08-02 11:12:14');

The output shows that the DATE_TRUNC() function resets all the values of the input timestamp(other than the specified date part, i.e., “Year” in the above example) to their initials.

7) Postgres EXTRACT() Function

In Postgres, the built-in EXTRACT() function that works the same way as the DATE_PART() function:

• It is used to extract a particular field from the input TIMESTAMP.
• The return type of the EXTRACT() function is DOUBLE PRECISION.

Basic Syntax

EXTRACT(Field FROM Source);

The above syntax shows that the EXTRACT() function accepts two arguments: a field and a source. The EXTRACT() will extract the specified field from the given source.

Example: How Does the EXTRACT() Function Work in Postgres?

Suppose we need to extract the minutes from the current date/time; to do that, we will utilize the EXTRACT() function as follows:

SELECT EXTRACT('MIN' FROM NOW());

The output snippet shows that minutes were successfully extracted from the current date and time.

8) Postgres TO_DATE() Function

Postgres offers an in-built TO_DATE() function that is used to convert a string/text into a date:

• It accepts a string and a date format as arguments.
• The date format must be a valid pattern/format, as demonstrated in the following blog: “Postgres’ TO_DATE() Function”.
• It converts the input string into a date based on the defined format.
• The return type of the TO_DATE() function is “DATE”.

Basic Syntax

TO_DATE(string, date_format);

The above snippet indicates that the TO_DATE() function accepts a string and a date_format as an argument. Consequently, it will convert the input string into a date based on the specified date_format.

Example: How Does the TO_DATE() Function Work in Postgres?

Execute the below-provided command to see how it works:

SELECT TO_DATE('2022-12-09','YYYY-MM-DD');

The output authenticates that the given string has been converted into the DATE data type.

9) Postgres AGE() Function

Postgres provides a built-in AGE() function that accepts two dates as arguments and finds the interval between the given dates.

• The second time stamp will be subtracted from the first one, and the resultant interval will be returned as output.
• Return Type: INTERVAL.

Basic Syntax

AGE(TIMESTAMP_1, TIMESTAMP_2);

The above snippet shows that the AGE() function accepts two timestamps/dates as arguments.

Example: How Does the AGE() Function Work in Postgres?

Let’s suppose we want to calculate the age of a person whose birth date is “1990-01-01”. For this purpose, we will execute the AGE() function as follows:

SELECT AGE(CURRENT_DATE, '1990-01-01');

The output shows that the AGE() function calculates the age and retrieves an INTERVAL.

How to Format a Date in Postgres?

In Postgres, the TO_CHAR() function is used to format a date value to a specific date format:

• TO_CHAR() function accepts two arguments: a date and a valid format mask.
• The following blog describes the valid formats: “How to format a date in Postgres”.
• The Return type of the TO_CHAR() function is TEXT.

Basic Syntax

TO_CHAR(field, format);

Example 1: How to Format a Date Using TO_CHAR() Function?

Let’s pass a date and a valid format of your choice to format the input date into the specified format:

SELECT TO_CHAR('12 Jan, 2022'::DATE, 'YYYY-MM-DD');

In the above example:

• The TO_CHAR() function accepts a date in the “DD Mon, YYYY” format as the first argument.
• The cast operator “::” is used to perform the type conversion.
• A valid date format, “YYYY-MM-DD”, is passed to the TO_CHAR() function.
• Consequently, the given date will be formatted into the “YYYY-MM-DD” format, as shown in the following snippet:

The output snippet proves that the given date has been formatted into the user-specified date format.

Example 2: How to Format Current Date Using TO_CHAR() Function?

Suppose we want to format the current date into “DD Mon, YYYY” format; for this, we will utilize the TO_CHAR() function as follows:

SELECT TO_CHAR(NOW(), 'DD Mon, YYYY');

In this example, the TO_CHAR() function takes two values as arguments:

1) The NOW() function is passed as a first argument to the TO_CHAR() function that will retrieve the current date in “YYYY-MM-DD” format.
2) A date format is passed to the TO_CHAR() function. The date retrieved by the NOW() function will be formatted according to the given format.

The output shows that the current date has been formatted according to the specified format.

How to Get an Interval Between Two Dates in Postgres?

In PostgreSQL, the “-” operator and the AGE() function are used to get an interval between the two dates. Use the following syntax to get the interval via the “-” operator:

'date_1'::DATE - 'date_2'::DATE;

The above query will retrieve the days between the given dates.

Example 1: How to Get the Interval Between the Two Dates Using “-” Operator?

Execute the following query to get the total days between the input dates:

SELECT '2022-12-12'::DATE - '2021-11-01'::DATE;

The output shows that the difference between the given dates is 406 days.

Example 2: How to Use the “-” Operator on the Table’s Data?

Suppose we want to find the total duration of the published articles. For this purpose, we can use the “-” operator as follows:

SELECT (CURRENT_DATE - published_date) total_days
FROM published_articles;

In the above snippet, we utilized the “-” operator to find the interval between the current date and the articles’ published date:

The output snippet shows that the “-” operator finds the interval between the current_date and published_date successfully.

Note: You can also use the AGE() function to achieve the same functionality.

Conclusion

In Postgres, the DATE data type stores or manipulates the date values. It stores the dates in “YYYY-MM-DD” format. Moreover, Postgres offers various built-in functions and operators to work with the dates. For instance, the functions like NOW(), CURRENT_DATE, etc., are used to obtain today’s date. The EXTRACT(), DATE_PART() function extracts a specific date part. The “-” operator and AGE() function are used to find an interval between two dates, and so on. This blog post explained date data types, functions, operators, formats, and intervals with the help of appropriate examples.