In order to extract time part (hour, Minutes and Seconds) from datetime(timestamp) in PostgreSQL. We will be using CAST() function Or ::(CAST Notation). In a roundabout way we can also use extract() function to extract only time part from date in PostgreSQL. Let’s see an example of all the three approaches. We will also consider an example on how to extract time part (hour, Minutes and Seconds) from datetime column PostgreSQL table.
Extract time part from datetime(timestamp) using CAST() function PostgreSQL:
We will be passing datetime into cast function and will mention AS TIME, which will extract only time part from datetime in PostgreSQL.
Example 1:
SELECT CAST('2024-02-24 15:30:45' AS TIME);
Example 2:
SELECT CAST('2024-02-24 15:30:45.791354' AS TIME);
Extract time part from datetime column PostgreSQL table – CAST() Function:
We will be using below Student_detail table for our example to depict on how to extract time part from datetime column in postgresql.
Student_detail:
We will be using cast() function, with the column , along with AS TIME Keyword to extract time from datetime (timestamp) in PostgreSQL.
select *, CAST(birthdaytime AS TIME) as birthtime_only from student_detail
“birthdaytime” (datetime) column is used to extract only time part and stored in new column named “birthtime_only”
Extract time part from datetime(timestamp) using CAST Notation (::) PostgreSQL:
We will be passing datetime along with cast notation (::) ::time, which will extract only time part from datetime in PostgreSQL.
Example 1:
SELECT '2024-02-24 15:30:45'::time AS extracted_time;
Example 2:
SELECT '2024-02-24 15:30:45.791354'::time AS extracted_time;
Extract time part from datetime column PostgreSQL table:
We will be using below Student_detail table for our example to depict on how to extract time part from datetime column in postgresql.
Student_detail:
We will be using cast notation (::) along with the column , to extract time from datetime (timestamp) in PostgreSQL.
SELECT *,birthdaytime::timestamp::time AS birthtime_only FROM student_detail;
birthdaytime” (datetime) column is used to extract only time part and stored in new column named “birthtime_only”
Extract time part from datetime(timestamp) PostgreSQL:
This is a roundabout way. We will be extracting Hour, Minute and Seconds from Timestamp (datetime) and will be concatenating them to create time part alone.
Example 1:
SELECT EXTRACT(HOUR FROM '2024-02-24 15:30:45'::timestamp)::text || ':' || EXTRACT(MINUTE FROM '2024-02-24 15:30:45'::timestamp)::text || ':' || EXTRACT(SECOND FROM '2024-02-24 15:30:45'::timestamp)::text AS formatted_time;
Extract time part from datetime column PostgreSQL table:
We will be using below Student_detail table for our example to depict on how to extract time part from datetime column in postgresql.
Student_detail:
We will be extracting hour, minutes and seconds from birthdaytime column and concatenate them to create only Time part from datetime (timestamp) column in PostgreSQL.
select *, EXTRACT(HOUR FROM birthdaytime::timestamp)::text || ':' || EXTRACT(MINUTE FROM birthdaytime::timestamp)::text || ':' || EXTRACT(SECOND FROM birthdaytime::timestamp)::text AS formatted_time from student_detail
OUTPUT
