PostgreSQL is a free and open-source advanced database management system that offers numerous built-in date, and time functions. These functions allow us to perform different tasks on the DateTime values efficiently. In database management systems as well as in programming paradigms converting a timestamp to date is a common task.

Converting a timestamp to a date assists us in data manipulation and analysis. Postgres offers various ways to convert a TIMESTAMP to a DATE, such as TO_CHAR() function, CAST operator, EXTRACT function, etc.

This blog illustrates the following methods to convert a timestamp to date in Postgres:

- Method 1: Using Postgres DATE() Function
- Method 2: Using Postgres CAST Operator
- Method 3: Using Postgres TO_CHAR() Function
- Method 4: Using Scope Resolution “::” Operator
- Method 5: Using Postgres EXTRACT() Function
- Method 6: Using Postgres DATE_PART() Function

Let’s start with the DATE() function.

Method 1: Using Postgres DATE() Function

The built-in DATE() function is one of the most convenient ways of converting a timestamp to a date. For instance, the following code snippet demonstrates how to convert the current timestamp to date using the DATE() function:

SELECT CURRENT_TIMESTAMP, DATE(CURRENT_TIMESTAMP);

- The CURRENT_TIMESTAMP function returns today’s DateTime(timestamp) With timezone.
- Passing the CURRENT_TIMESTAMP to the DATE() function will convert the current timestamp to the current date.
- The below snippet shows the “current timestamp” and the “current timestamp converted to date”:

Method 2: Using Postgres CAST Operator

The CAST operator in Postgres allows us to convert one data type to another. Similarly, a timestamp can be converted to a date using the CAST operator, as shown in the following code snippet:

SELECT CAST('2023-01-09 20:41:12.791354-08' AS DATE);

In the above query:

- A timestamp is passed as an argument to the CAST operator.
- “AS DATE” represents that the given timestamp should be converted into a date.

Method 3: Using Postgres TO_CHAR() Function

The TO_CHAR() function assists us in converting the given timestamp into a date using a specific format. Here is an example:

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') As Current_Date;

- The NOW() function is passed as an argument to the TO_CHAR() function.
- The NOW() function returns today’s DateTime.
- The “YYYY-MM-DD” format is passed as an argument to the TO_CHAR() function.
- The TO_CHAR() function will convert the given timestamp to the specified date format.

Method 4: Using Scope Resolution “::” Operator

Alternatively, the scope resolution operator can be used to convert a specific timestamp into a date. For this purpose, specify the DATE data type along with the scope resolution operator, as shown in the following snippet:

SELECT '2023-01-09 20:41:12.791354-08' :: DATE;

The given timestamp has been successfully converted to a date using the scope resolution operator.

Method 5: Using Postgres EXTRACT() Function

Postgres offers another DateTime function named EXTRACT() that enables us to extract only a specific field from the given timestamp. In the following snippet, the “month” is passed as the first argument to the EXTRACT() function. Consequently, the EXTRACT() function will extract the month from the given timestamp:

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) as current_month;

The output shows that it’s the second month of the year, i.e. February.

Method 6: Using Postgres DATE_PART() Function

The DATE_PART() function offers the same functionality as the EXTRACT() function. The following snippet will provide you with more clarity regarding the DATE_PART() function:

SELECT DATE_PART(YEAR, CURRENT_TIMESTAMP) as current_year;

The year from the current timestamp has been extracted using the DATE_PART() function.

Conclusion

Postgres offers various ways to convert a TIMESTAMP to a DATE, such as TO_CHAR() function, CAST operator, EXTRACT function, etc. The DATE() function, scope resolution operator, cast operator, and TO_CHAR() functions are used to convert a timestamp to a date. While the DATE_PART() and EXTRACT() functions are used to convert a timestamp to a specific date field, such as a year, month, day, etc. This blog presented a detailed guide on converting the timestamp to a date in PostgreSQL.