PostgreSQL Date Part Hour - DatabaseFAQs.com

In this PostgreSQL tutorial, we will discuss PostgreSQL Date Part Hour to get the hour value from the timestamp. Here we will use the function date_part() to extract the hour component with different examples such as from the current timestamp, interval, and table.

Table of Contents

What is PostgreSQL Date Part Hour

The date_part() function in PostgreSQL returns the particular components of the given date or time value. The component depends upon what kind of unit (like a year, month, second and etc) is specified in the date_part() function.

So here you will learn about the unit ‘hour’ or how to get the hour component from the given timestamp.

The syntax is given below for how to use the date_part() function with unit ‘hour’.

date_part('hour', timestamp_value)

hour: It is the unit that will be extracted from the given timestamp.
timestamp_value: It is the date and time or called the timestamp value from which component ‘hour’ is extracted.

PostgreSQL Date Part Hour with CURRENT_TIMESTAMP

When you want to get or extract the current hour from the current time, use the below command.

SELECT date_part('hour', CURRENT_TIMESTAMP);

PostgreSQL Date Part Hour with CURRENT_TIMESTAMP

In the above query within the date_part() function, provided the two values, the unit or the component ‘hour’ and the CURRENT_TIMESTAMP function that returns the current time your system. The output shows that the current system hour from the timestamp value is 14.

The real value returned by the CURRENT_TIMESTAMP was ‘2023-06-17 14:45:08.52928+05:30’, the date_part() function extracted only the hour component of the timestamp.

PostgreSQL Date Part Hour From Interval

You can also use the INTERVAL value with the date_part() function that represents the duration of any event. Mean you can get the hour component from the interval value.

The syntax is given below.

SELECT date_part('hour', INTERVAL 'interval_value');

INTERVAL: It is the keyword to tell the date_part() function that we are using as an interval value for the extraction of the component hour.
‘interval_value’: It is the exact interval value like ‘1 day 3 hours 4 minutes’.

Let’s take an example to see how to get the hour component from the interval value.

SELECT date_part('hour', INTERVAL '7 days 5 hours 10 minutes');

In the above query, provide the interval value as ‘7 days 5 hours 10 minutes’ to the date_part() function with a unit equal to ‘hour’. The function returns the hour component which is 5 in this case.

PostgreSQL Date Part Hour from Table

Think about a situation where you need to extract the hour component from the timestamp stored in the table. For that, you can also use the date_part() function.

The syntax is given below.

SELECT date_part('hour', column_name) FROM table_name;

column_name: This is the name of the column that stores a timestamp value.
table_name: The name of the table where a column exists with a timestamp value.

Suppose you have a table called ’employee_login_time’ with columns ’employee_id’ and ‘login_time’, the column ‘login_time’ stored the information about the login timing of each employee on the portal of company.

So here we are going to extract the login hour of each employee using the below query.

SELECT date_part('hour',login_time) FROM employee_login_time;

The above output shows that the login hour of each employee is 10. So in that query extracted the hour component from the column of type timestamp that exist in the table.

Conclusion

In this PostgreSQL tutorial, we have discussed how to extract the hour value from the current timestamp of the system, the interval value, and the column of type timestamp that exists in the table.