PostgreSQL DATE Functions Examples

In this PostgreSQL tutorial, we will learn about PostgreSQL DATE Functions Examples and know how to extract specific parts of the dates, manipulate the dates, perform arithmetic operations on dates, and convert dates or times to different time zone.

PostgreSQL DATE Functions Examples

Let us discuss various examples one by one in detail.

Example 1: Getting the current date and time

In PostgreSQL, to retrieve the exact time or moment a query is executed, two functions are used to obtain the current data with time.

The following are the standard functions:

CURRENT_DATE: This function retrieves the current date, as shown in the example below.

SELECT CURRENT_DATE;

The “CURRENT_DATE” function is used in conjunction with a “SELECT” statement in the above example to retrieve the current date, which is 2025-07-15.

CURRENT_TIMESTAMP: This function retrieves the current date and time, as shown in the example below.

SELECT CURRENT_TIMESTAMP;

The function “CURRENT_TIMESTAMP” is used with the “SELECT” statement in the above example to retrieve the current date and timestamp, which is “2025-07-15 17:43:24.601521+05:30”.

Example 2: Extracting date parts

PostgreSQL offers several functions for manipulating dates, including extracting specific parts (such as month, year, or day), adding or subtracting intervals from dates, and formatting dates.

Some of the functions are shown below with examples:

DATE_PART: It accepts the date or timestamp and extracts the particular part, such as the month, day, and year.

SELECT DATE_PART('month', DATE '2025-7-16');

The function “DATE_PART” in the above example extracted the month part of the date “2025-7-16”, and the month part of the date is 7. This function accepts two values: the first is the part (such as a year, month, or day) of the date you want, and the second is the date value.

Now take on more examples and extract the year part of the same date as shown in the picture below.

SELECT DATE_PART('year', DATE '2025-7-16');

In the above output, the “DATE_PART” function extracted the year part, which is 2025.

DATE_TRUNC: This function truncates the date or timestamp to the provided precision, such as (year, month, hour, day, etc).

SELECT DATE_TRUNC('year', DATE '2025-7-16');

The above function, “DATE_TRUNC”, in the example truncated the year part of the given date, “2025-7-16”, but you can see in the output how the truncated part is presented as “2025-01-01”.

Let’s check another component of the data, such as a month, and how the “DATE_TRUNC” function outputs this information.

Again, when we truncated the “month” part of the date “2025-7-16”, we got the output “2025-07-01”, which represents the month as 7.

DATE ADDING: The interval is added to a date or timestamp. The interval can be a year, month, day, hour, minute, or second.

SELECT DATE '2023-6-16' + INTERVAL '4 day';

In the above example, I added the 4 days to the date “2023-6-16” and after adding the interval, the date becomes “2023-06-20”. To add the interval to the date, use the keyword “+ INTERVAL” and specify the interval within single quotes, e.g., ‘4 day | 1 hour’, etc.

Take on more examples and add the month’s interval to the date; check the example below.

The output shows that the month of the date changed to the next month, “2023-07-16”.

DATE SUBTRACTING: You can also subtract the interval from the specified date.

SELECT DATE '2023-6-16' - INTERVAL '1 month';

In the above output, the interval of 1 month was subtracted from the date “2023-6-16”, and after subtracting, the date becomes “2023-05-16”.

Example 3: Arithmetic Calculations

PostgreSQL provides functions that can be used to perform arithmetic operations on dates, such as calculating the difference between two dates.

Some of the functions are:

AGE: To get the difference between two dates, use this function.

SELECT AGE(DATE '2023-06-16', DATE '2023-05-16');

In the above example, the “AGE” function computed the difference between two dates, “2023-06-16” and “2023-06-16”, which is 1 month.

EXTRACT: To get the specific part of the date, use this function.

SELECT EXTRACT(DAY FROM DATE '2022-06-16');

In the above example, we extracted the day from the given date “2022-06-16“, which is 16 in this case. To get the other components of the dates, like year and month, then use this YEAR or MONTH in the place of DAY within the EXTRACT (DAY FROM DATE ‘2022-06-16’) function.

Example 4: Using For Formatting

PostgreSQL has functions to format dates according to predefined patterns. These functions enable us to display dates in various formats for easier readability or more suitable presentation.

Standard functions are given below:

TO_DATE: This function uses a specified format to convert a string to a date.

SELECT TO_DATE('2023-05-12', 'YYYY-MM-DD');

In the above example, the date in the string ‘2023-05-12’ format is converted into the specified format ‘YYYY-MM-DD’, as shown in the output. The function “TO_DATE(‘string_date’, ‘format’)” accepts two parameters: the date in string type and the format of the date, how it should be presented.

TO_CHAR: To convert a date or timestamp into a string of a particular format, use this function.

SELECT TO_CHAR(DATE '2023-06-16', 'YYYY-MM-DD');

In the above example, convert the date “2023-06-16” type into the date string type in the specified format ‘YYYY-MM-DD’. This function takes two values, the first is the date value, and the second specific format for converting the dates.

Example 5: Converting timestamps into a specific time zone

PostgreSQL provides a function to handle time zone conversion and retrieve time zone information when working with date and time data across different time zones.

AT TIME ZONE: To convert timestamps into a specific time zone, use this function.

SELECT TIMESTAMP '2023-06-16 17:44:45' AT TIME ZONE 'GMT-5';

In the above example, the timestamp ‘2023-06-16 17:44:45’ was converted to the Chicago time zone (GMT-5) by specifying the AT TIME ZONE ‘GMT-5’ after the timestamp value.

Performance Considerations

When working with date functions, keep these performance tips in mind:

1. Create indexes on date columns that are frequently used in WHERE clauses
2. Use DATE_TRUNC for range queries rather than complex expressions
3. Consider materialized views for frequently run date-based reports
4. Be cautious with time zones, as they can lead to unexpected results

Conclusion

In this PostgreSQL tutorial, we have covered how to find the current date and get the specific part of the date, like month, year, and day. Also learned about performing arithmetic operations on dates, such as calculating the age between two dates, formatting dates, and converting dates or times to different time zones.

You may like to read:

• PostgreSQL WHERE Clause
• PostgreSQL WHERE IN Condition
• How to Create Database in PostgreSQL