PostgreSQL Date Functions

Last Updated : 01 Nov, 2024

PostgreSQL is widely recognized for its comprehensive support for date and time manipulations, making it an excellent choice for applications requiring precise time management and complex calculations.

This article explores the core PostgreSQL date functions, covering how to retrieve the current date, extract date components, perform conversions, handle intervals, and use operators and utility functions.

PostgreSQL Date Functions

PostgreSQL offers a rich set of functions for manipulating dates and times, making it one of the most robust relational database systems for applications that require precise time management. This guide covers essential date functions, how to retrieve current dates and times, extract components, perform conversions, work with intervals, and use date arithmetic and formatting utilities.

Section 1: Getting the Current Date and Time

In PostgreSQL, retrieving the current date and time can be accomplished using several built-in functions. This is important for tracking timestamps in applications.

Syntax

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT NOW();

Explanation:

CURRENT_DATE: It is return the today's date.
CURRENT_TIME: It is return the current time.
NOW(): It is return both current date and current time including time zone.

Example

SELECT NOW();

Output

2024-10-23 9:30:56.789123+00

The above query returns current date and current time, including the time zone.

Section 2: Extracting Date and Time Components

To extract specific date and time components, such as the year, month, or day, PostgreSQL provides the EXTRACT() function. This function is handy for analyzing parts of a date or timestamp.

Syntax

SELECT EXTRACT(field FROM source);

Explanation:

EXTRACT(): It is retrieve the part of a date/time value such as year, month, day.
The field can YEAR, MONTH, DAY, HOUR, etc.

Example

SELECT EXTRACT(YEAR FROM CURRENT_DATE);

Output

Explanation:

In this example, EXTRACT(YEAR FROM CURRENT_DATE) extracts the year component from the current date. This function is often used to filter data by specific date parts.

Section 3: Converting to Date and Time

PostgreSQL is provide the conversion functions like TO_DATE() and TO_TIMESTAMP() convert the strings into the date and time formats.

Syntax

SELECT TO_DATE('text', 'format');
SELECT TO_TIMESTAMP('text', 'format');

Explanation:

TO_DATE(): This function is convert the string into the date.
TO_TIMESTAMP(): This function is convert the string into the timestamp.

Example

SELECT TO_DATE('2024-10-23', 'YYYY-MM-DD');

Output

'2024-10-23'

Explanation:

Here, TO_DATE('2024-10-23', 'YYYY-MM-DD') converts the string '2024-10-23' into a date format. This function is critical for data consistency when dealing with different date formats.

Section 4: Handling Intervals

Intervals in PostgreSQL represent time spans and are used for adding or subtracting time from a date. This is useful for applications that calculate due dates, deadlines, or schedules.

Syntax:

SELECT date + INTERVAL 'interval_value';
SELECT date - INTERVAL 'interval_value';

Explanation:

INTERVAL: It is define the time span, example: years, months, days.

Example:

SELECT NOW() + INTERVAL '1 month';

Output

'2024-11-23 10:34:56.789123+00'

Explanation:

In this example, NOW() + INTERVAL '1 month' adds one month to the current date and time. Using intervals is beneficial for scheduling and dynamic time-based calculations.

Section 5: Operators

We can perform the date arithmetic directly by adding or subtracting the date and intervals with the help of operators such as + and - .

Syntax

SELECT date1 - date2;
SELECT date + INTERVAL 'n units';

Explanation:

We can subtract the one date from another date or add an interval to the date.

Example:

SELECT '2024-10-23' - '2023-10-23';

Output

'365 days'

Explanation:

The above query is calculate the difference between the two dates. The difference between the above two dates is 365 days.

Section 6: Utility Functions

PostgreSQL is offer the several utility functions to format and adjust the date/time values, like TO_CHAR() for formatting and JUSTIFY_DAYS() for the adjusting intervals.

Syntax

SELECT TO_CHAR(source, 'format');
SELECT JUSTIFY_DAYS(interval);

Explanation:

TO_CHAR(): This is used to convert the date or timestamp into the formatted string.
JUSTIFY_DAYS(): This is used to adjust the intervals to the more human-readable format.

Example 1

SELECT TO_CHAR(NOW(), 'Day, DDrd Month YYYY');

Output

'Wednesday, 23rd October 2024'

The above query is convert the current date into the readable string.

Example 2

SELECT JUSTIFY_DAYS(INTERVAL '40 days');

Output

'1 mon 10 days'

Explanation:

The above query is adjust the 40 days into the more human-friendly format, that means 40 days is 1 month and 10 days.

Conclusion

PostgreSQL’s extensive date and time functions provide powerful tools for managing and manipulating date and time data. Whether we need to retrieve the current date, perform date arithmetic, extract specific date components, or format dates for readability, PostgreSQL has a function for it. These capabilities make PostgreSQL an ideal choice for applications that require precise time management and complex date calculations.