PostgreSQL DATE Functions with Examples

In this PostgreSQL tutorial, we will learn about PostgreSQL DATE Functions with 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 Current Date Functions

In PostgreSQL, to get the exact time or moment a query is executed, there are two functions that get the current data with time.

The following are the common functions:

CURRENT_DATE: This function retrieves the current date as you can see in the below example.

SELECT CURRENT_DATE;

The function “CURRENT_DATE” is used with a “SELECT” statement in the above example to get the current date which is 2023-06-16.

CURRENT_TIMESTAMP: This function retrieves the current date with time as you can see in the below example.

SELECT CURRENT_TIMESTAMP;

The function “CURRENT_TIMESTAMP” is used with the “SELECT” statement in the above example to get the current date with the timestamp also which is “2023-06-16 10:58:01.330202+05:30”.

PostgreSQL Date Functions Manipulation

PostgreSQL has several of functions for manipulating the dates such as extracting specific parts of dates (like a month, year, or day), adding or removing the intervals to the date, and formatting the 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 '2023-6-16');

The function “DATE_PART” in the above example extracted the month part of the date “2023-6-16” and the month part of the data is 6. This function accepts two values, the first value is the part (like a year, month, or day of the date) you want from the date 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 below picture.

SELECT DATE_PART('year', DATE '2023-6-16');

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

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 '2023-6-16');

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

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

Again when we truncated the “month” part of the date “2023-6-15”, we got the output like this “2023-06-01” which represents the month is 6.

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, 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 a single quote ‘4 day | 1 hour’ etc.

Take on more examples and add the interval of the month to the date, check the below example.

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, subtracted the interval of 1 month from the date “2023-6-16” and after subtracting the date becomes “2023-05-16”.

PostgreSQL Date Functions Arithmetic Functions

PostgreSQL has functions that can be used to perform some arithmetic operations on the dates such as computing 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 mon or 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, extracted the day from the given date “2022-06-16” which is 16 in this case. To get the other component 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.

PostgreSQL Date Functions For Formatting

PostgreSQL has functions to format dates according to predefined patterns. These functions allow us to display dates in various ways for easier-to-read or more appropriate presentation.

Common 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, converted the date in string ‘2023-05-12’ format into the specified format ‘YYYY-MM-DD’ date that you can see 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, converted 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.

PostgreSQL Date Functions Time Zone

PostgreSQL has a function to handle time zone conversion and get time zone information when working with date and time data for different time zones.

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

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

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

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 of the two dates with how to format the dates and convert the dates or times to different timezone.

You may like to read:

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