Users can store time and date using the timestamp data type. But sometimes they need to get the time or the date part from the timestamp. Users can extract the time part from the timestamp data type by casting the timestamp. In this article, we will learn how can we extract the time part from the timestamp in PostgreSQL.

How to EXTRACT TIME Part From a TIMESTAMP in PostgreSQL?

The time can be extracted from the timestamp in PostgreSQL by typecasting the timestamp to time.

Let’s learn this using an example.

Example: Extracting Time Part From a TIMESTAMP

Consider the following query:

SELECT TIMESTAMP '2023-11-09 16:41:15'::time ;

The above query is meant to extract time from the given timestamp. The output of the query is:

In this way, we can get the time from the timestamp. Let’s implement the same query for the table.

Example: Extracting Time Part From a TIMESTAMP Column

Consider the table named “store_sales_details”.

Now if we want to get the time from the whole column, we will first have to typecast the column to the timestamp data type and then to the time. The query can be written like this:

SELECT *, time_and_date_of_order:: timestamp:: time FROM store_sales_details;

In the above syntax:

We first type-casted the whole column as a timestamp and then as time. The output will give another column containing all the times extracted from timestamps.

The output looks like this:

You can see that we have extracted the time from the timestamps through a PostgreSQL query.

Conclusion:

We can extract the time from the timestamp by type-casting the timestamp into the time. Getting the time from the timestamp may be of great interest in many cases such as in this case when doing the analysis for the time when the product is generating sales and performing well. In this article, we have learned to extract the time from the timestamp with examples.