How HOUR works in SQL? Best HOUR examples

How HOUR works in SQL? Best HOUR examples

When working with time-based data in SQL, extracting specific time components is often necessary. One function that comes in handy is HOUR(). It allows us to extract the hour from a given time or datetime value. In this article, I’ll walk you through how HOUR works in SQL, illustrate it with examples, and discuss practical use cases.

Understanding the HOUR Function in SQL

The HOUR() function extracts the hour portion (0-23) from a time or datetime value. It’s useful when analyzing hourly trends, grouping data by hour, or simply retrieving the hour for conditional filters.

Basic Syntax of HOUR() in SQL

The basic syntax of the HOUR function is:

HOUR(expression)

Where expression is either a DATETIME, TIME, or TIMESTAMP value.

HOUR Function in Action

Let’s take some practical examples to understand how this function works in different scenarios.

Extracting Hour from a TIME Value

SELECT HOUR('14:30:15') AS extracted_hour;

Output:

extracted_hour
14

Extracting Hour from a DATETIME Value

SELECT HOUR('2024-06-05 08:45:30') AS extracted_hour;

Output:

extracted_hour
8

Using HOUR with a TIMESTAMP Column

If we have a table named orders with a column order_time of type DATETIME, we can extract hours as follows:

SELECT order_id, HOUR(order_time) AS order_hour FROM orders;

Common Use Cases for HOUR

The HOUR() function is useful in many scenarios, such as:

  • Analyzing hourly sales trends.
  • Grouping records based on hour intervals.
  • Filtering records where the hour matches a certain condition.
  • Combining with other date/time functions to refine data analysis.

Filtering Data Using HOUR()

Suppose we only want to retrieve orders placed between 9 AM and 12 PM. We can use HOUR() in a WHERE clause:

SELECT * FROM orders WHERE HOUR(order_time) BETWEEN 9 AND 12;

Combining HOUR with GROUP BY

If we want to count the number of orders per hour, we can group data accordingly:

SELECT HOUR(order_time) AS order_hour, COUNT(*) AS total_orders
FROM orders
GROUP BY order_hour
ORDER BY order_hour;

HOUR vs Other Time Extraction Functions

In addition to HOUR(), SQL provides other functions for extracting time components:

  • MINUTE() – Extracts the minute.
  • SECOND() – Extracts the second.
  • DAY() – Retrieves the day.
  • MONTH() – Retrieves the month.
  • YEAR() – Retrieves the year.

Conclusion

The HOUR() function in SQL is an essential tool when dealing with time-based data. It allows us to retrieve, analyze, and filter records based on the hour of the day. Whether you’re working with timestamps, grouping data by hour, or filtering specific timeframes, HOUR() is invaluable in SQL queries.

 

Other interesting article:

How DAY works in SQL? Best DAY examples