In order to get second from Timestamp(datetime) in postgresql we use Extract() Function. Within the Extract() function we have to mention SECOND as we are getting SECOND from timestamp. To extract seconds from datetime i.e. timestamp in PostgreSQL, you can use the DATE_PART() function with the ‘seconds’ unit.
Let’s see how to
- Extract second from Timestamp in Postgresql
- Create the column which extracts second from timestamp column
With an example for both
Extract second from Timestamp(datetime) in Postgresql:
Within the Extract keyword we have to mention SECOND as we are getting second from timestamp
SELECT EXTRACT (SECOND FROM TIMESTAMP '2011-11-22 13:30:15')
So the resultant SECOND will be
Syntax for DATE_PART() Function in Postresql:
DATE_PART(unit, source)
unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).
source: The date or timestamp from which to extract the specified part.
In Our case. The values of the unit must be “seconds”.
Extract seconds from datetime(timestamp) in postgresql Simple Example:
In order to Extract seconds from datetime (timestamp) in postgresql we will be using DATE_PART() function.
In the below Example we will be passing seconds as “unit” argument and datetime as “source” argument in order to extract seconds from Date or datetime
SELECT DATE_PART('seconds', '2024-02-24 15:30:45.123456'::timestamp) AS seconds_part;
Output:
Extract seconds from Current date in postgresql :
This query uses the CURRENT_TIMESTAMP function to get the current datetime and then extracts the econds using DATE_PART()
SELECT CURRENT_TIMESTAMP AS current_datetime, DATE_PART('seconds', CURRENT_TIMESTAMP) AS seconds;
Output:
Extract seconds from Date in postgresql table:
We will be using Below Student_detail table for our example to depict on how to extract seconds from date in postgresql
Student_detail:
In the above table we will be using DATE_PART() Function, which will take “seconds” and column named “birthdaytime” as argument. Which will extract seconds from “birthdaytime” column and store in the new column of postgresql table as shown below
select *, DATE_PART('seconds', birthdaytime::timestamp) AS seconds from Student_detail
so the resultant table will have seconds column
Extract Seconds from Date/Timestamp in postgresql table using Extract():
We will be using Below Student_detail table for our example to depict on how to extract Seconds from date in postgresql
Student_detail:
In the above table we will be using EXTRACT() Function, which will take “Seconds” and column named “birthdaytime” as argument. Which will extract Seconds from “birthdaytime” column and store in the new column of postgresql table as shown below
Select *, extract(SECONDS FROM birthdaytime) as seconds from student_detail
So the resultant table will have seconds column