EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL HOUR()

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL HOUR()

SQL HOUR()

Introduction to SQL Hour()

HOUR() function is a date/time function in standard query language (SQL) that is used to extract the hour part from a given datetime or timestamp data. This function is primarily supported in databases such as MYSQL and ORACLE. While other popular database management servers such as PostgreSQL and SQL Server use other similar functions, EXTRACT( ‘hour’ from ‘date_expression’) and DATE_PART(‘hour’ from ‘date_expression’). In this article, we will be discussing the HOUR() function in particular with the help of some practical examples. To begin with, let’s discuss the syntax and parameters used in this function.

Syntax and Parameters

The basic syntax for HOUR() function in MYSQL and ORACLE databases is as follows :

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

HOUR(‘expression’)

The parameter and output of the function is as follows :

  • expression: expression is the input value. It can be of date, datetime, timestamp, or string convertible to similar data type.
  • Output: The function returns an integer value corresponding to the value of the hour part of the given expression in 24-hour format.

The hour function can be used as a part of the SELECT statement for selecting the hour part, WHERE OR HAVING clause to filter based on the hour part in the filtering expression, ORDER BY clause to sort records based on the hour part of the expression or GROUP BY clause to group records by hour part of a given date/timestamp column.

Having discussed the basic syntax and parameters used for working with HOUR() function, let us go ahead and discuss a few examples to understand the functionality in great detail.

Examples of SQL HOUR()

Following are the examples are given below:

Example #1

Extract the hour part from the given timestamp/datetime/time expressions.

1. 24:48:59

SELECT HOUR('24:48:59');

SQL HOUR()-1.1

2. 2020-09-22 15:09:00

SELECT HOUR("2020-09-22 15:09:00");

SQL HOUR()-1.2

3. 2020-09-22 12:14:49 GMT

SELECT HOUR("2020-09-22 12:14:49 GMT");

SQL HOUR()-1.3

In the above mentioned example, you might have noticed three different kinds of datetime/timestamp expressions. In the first one, we just have time, in the second one, we have datetime and in the third one, we have a timestamp along with the timezone. The hour function of MYSQL/Oracle works well with all such data types.

Popular Course in this category
Sale
MS SQL Training (14 Courses, 11+ Projects)14 Online Courses | 11 Hands-on Projects | 62+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,993 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)SQL Training Program (7 Courses, 8+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

In order to illustrate the usage of HOUR() function, let’s create a table ‘orders’ with order_id, order_amount and order_time as columns. The CREATE statement for the orders table looks something as follows:

CREATE TABLE orders(
order_id VARCHAR(10),
order_amount REAL,
order_time DATETIME
);

After creating the orders table, insert a few records in it to work with. You may use the following INSERT statement for the same.

INSERT INTO orders (order_id,order_amount,order_time)
VALUES ('o1',345,'2020-09-22 10:23:32'),
('o2',143.54,'2020-09-22 10:13:20'),
('o3',564.32,'2020-09-22 12:23:32'),
('o4',345,'2020-09-22 11:11:21'),
('o5',125.78,'2020-09-22 11:23:32'),
('o6',34.98,'2020-09-22 11:13:25'),
('o7',78.92,'2020-09-22 10:23:32'),
('o8',132.67,'2020-09-22 12:28:26'),
('o9',786.54,'2020-09-22 10:37:29');

Having inserted the given records in the orders table, we are all set to try a few examples based on the HOUR() function with the help of this table.

Example #2

Find the order details for the orders with the hour of ordering, order_id and order_amount in the result set.

SELECT HOUR(order_time) as "Hour of Ordering", order_id, order_amount
FROM orders;

SQL HOUR()-1.4

In this example, we have used the HOUR() function as a part of the SELECT statement. The HOUR() function extracts the hour from the corresponding order_time column.

Example #3

Find the order details for the orders which were placed between 10 a.m. and 11 a.m. on 22nd September 2020.

SELECT order_id, order_amount, order_time
FROM orders
WHERE HOUR(order_time) >= 10 AND HOUR(order_time) < 11;

SQL HOUR()-1.5

Here, we have used HOUR() function as a part of WHERE clause to filter rows based on the hour of ordering. The function returns an integer value corresponding to the hour part of the given order_time column and compares it with the comparison expression.

Example #4

Sort the order details for the orders on 22nd September 2020 based on the hour of ordering, starting from earliest to latest.

SELECT order_id, order_amount, order_time
FROM orders
ORDER BY HOUR(order_time) ASC;

SQL HOUR()-1.6

In this example, we have used the HOUR() function to extract the hour part of the ordering time and then used it to order the fetched records in ascending order.

Example #5

Find the hourly revenue generated from the orders placed on 22nd September 2020.

SELECT HOUR(order_time) as "Hour of Ordering",
SUM(order_amount) as "Total_Revenue"
FROM orders
GROUP BY HOUR(order_time);

SQL HOUR()-1.7

Here, we have used HOUR() function as a part of the GROUP BY clause.

Conclusion

In this post, we have covered the HOUR() function in SQL, which is used to extract the hour part from a given input datetime/timestamp expression. The function returns an integer value between 0 and 24. This function is available only in MYSQL and Oracle databases.

Recommended Articles

This is a guide to SQL HOUR(). Here we also discuss the introduction and syntax and parameters of sql hour() along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. SQL Alias
  2. PostgreSQL String Array
  3. MySQL Math Functions
  4. SQL UNION

MS SQL Training (13 Courses, 11+ Projects)

14 Online Courses

11 Hands-on Projects

62+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - MS SQL Training (13 Courses, 11+ Projects) Learn More