In Postgres, timestamps can be handy for storing and tracking events, but sometimes you only need the date portion. For instance, you might want to group records by date, perform some analysis on just the date, etc. No matter what the case is, extracting the date from a timestamp in PostgreSQL is a straightforward process.

This post will cover the following methods for extracting the date from a timestamp in Postgres:

• How to Extract DATE From a TIMESTAMP Using DATE() Function?
• How to Extract DATE From a TIMESTAMP Using CAST Operator?
• How to Extract DATE From a TIMESTAMP Using Scope Resolution “::” Operator?

The approaches mentioned above will extract the date from the timestamp regardless of whether the timestamp contains or doesn’t contain timezone information.

Let’s begin with the DATE() function!

How to Extract DATE From a TIMESTAMP Using DATE() Function?

The DATE() function in Postgres is used to extract/get a date from a TIMESTAMP. The DATE() function takes a TIMESTAMP and extracts the date part from it. The following snippet shows the basic syntax of the DATE() function:

DATE(‘time_stamp’);

Specify the timestamp to be converted in place of the “time_stamp” argument.

Example 1: Extract the Date From a TIMESTAMP via the DATE() Function

Let’s pass a timestamp to the DATE() function and see how it works:

SELECT DATE('2022-11-15 02:46:28.158712-08');

The output proves that the DATE() function extracts the date part from the given timestamp successfully.

Example 2: Extract the Date From a TIMESTAMP Column Via the DATE() Function

We have defined a sample table in our database whose details are shown in the following snippet:

Let’s extract the date from the “joining_date” column via the DATE() function:

SELECT emp_id, emp_name, DATE(joining_date)
FROM employee_bio;

The output shows that the DATE() function extracts the date part from the targeted column successfully.

How to Extract DATE From a TIMESTAMP Using CAST Operator?

Using the CAST operator, you can also extract a date from a TIMESTAMP. Here is the syntax for extracting a date from a timestamp via the CAST operator:

CAST ('time_stamp' AS DATE);

Specify the timestamp to be converted in place of the “time_stamp” argument.

Example 1: Extract the Date From a TIMESTAMP Via the CAST Operator

Let’s learn how to extract a date part from the current timestamp via the CAST operator:

SELECT CAST(CURRENT_TIMESTAMP AS DATE);

The CURRENT_TIMESTAMP function will retrieve the current date and time with the timezone. The ‘CAST AS DATE’ operator will convert the current timestamp into a date:

The output snippet clarifies that the CAST operator successfully extracts the date from the timestamp.

Example 2: Extract the Date From a TIMESTAMP Column Via the CAST Operator

In this example, we will extract the date from the “joining_date” column of the “employee_bio” table via the CAST operator:

SELECT emp_id, emp_name, CAST(joining_date AS DATE)
FROM employee_bio;

The output snippet proves that the CAST operator extracts the date from the timestamp successfully.

How to Extract DATE From a TIMESTAMP Using Scope Resolution “::” Operator?

The scope resolution operator “::” works the same way as the CAST operator(i.e., converts the given timestamp to a date). The syntax for the scope resolution operator will go like this:

‘time_stamp':: DATE;

Specify the timestamp to be converted in place of “time_stamp”.

Example 1: Extracting a Date From a Timestamp Via the Scope Resolution Operator

In the following example, we will convert a timestamp into a date using the scope resolution operator:

SELECT '2022-11-15 02:46:28.158712-08':: DATE;

The output indicates that the date has been successfully extracted from the timestamp using the “::” operator.

Example 2: Extract the Date From a TIMESTAMP Column Via the Scope Resolution Operator

In this example, we will extract the date from the “joining_date” column of the “employee_bio” table via the scope resolution operator:

SELECT emp_id, emp_name, joining_date :: DATE
FROM employee_bio;

The output snippet clarifies that the date part has been successfully extracted from the timestamp.

Conclusion

In PostgreSQL, the built-in DATE() function, CAST operator, and scope resolution operator “::” are used to extract a date from a TIMESTAMP. All these methods take a timestamp/date-time value and convert it into a date. This blog presented various practical examples to explain how to extract a date from a timestamp in Postgres.