In order to get hour from Timestamp in postgresql we use Extract() function. Within the Extract keyword we have to mention HOUR as we are getting HOUR from timestamp. In addition to Extract() function we also use DATE_PART() function To extract hour from datetime i.e. timestamp in PostgreSQL, DATE_PART() function with the ‘hour’ unit accomplishes this task.
Let’s see how to
- Extract hour from Timestamp in Postgresql
- Create the column which extracts hour from timestamp column
With an example for both
Extract hour from Timestamp in Postgresql:
Within the Extract keyword we have to mention HOUR as we are getting hour from timestamp
SELECT EXTRACT (HOUR FROM TIMESTAMP '2011-11-22 13:30:15')
So the resultant HOUR will be
Syntax for DATE_PART() Function in PostgreSQL:
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 “hour”.
Extract hour from datetime(timestamp) in PostgreSQL Simple Example:
In order to Extract hour from datetime (timestamp) in postgresql we will be using DATE_PART() function.
In the below Example we will be passing hour as “unit” argument and datetime as “source” argument in order to extract hour from Date or datetime
SELECT DATE_PART('hour', '2024-02-24 15:30:45.123456'::timestamp) AS hour_part;
Output:
Extract hour from Current date in postgresql :
This query uses the CURRENT_TIMESTAMP function to get the current datetime and then extracts the hour using DATE_PART()
SELECT CURRENT_TIMESTAMP AS current_datetime, DATE_PART('hour', CURRENT_TIMESTAMP) AS hour;
Output:
Extract hour from Date in postgresql table using DATE_PART():
We will be using Below Student_detail table for our example to depict on how to extract hour from date in postgresql
Student_detail:
In the above table we will be using DATE_PART() Function, which will take “hour” and column named “birthdaytime” as argument. Which will extract hour from “birthdaytime” column and store in the new column of postgresql table as shown below
select *, DATE_PART('hour', birthdaytime::timestamp) AS hour from Student_detail
so the resultant table will have hour column
Extract hour from Date in postgresql table using Extract():
We will be using Below Student_detail table for our example to depict on how to extract hour from date in postgresql
Student_detail:
In the above table we will be using EXTRACT() Function, which will take “hour” and column named “birthdaytime” as argument. Which will extract hour from “birthdaytime” column and store in the new column of postgresql table as shown below
Select *, extract(HOUR FROM birthdaytime) as hour from student_detail
So the resultant table will have hour column