MySQL HOUR(), MINUTE() and SECOND()

By /
HOUR() MINUTE() SECOND()

In this tutorial, we will learn about the MySQL HOUR(), MINUTE() and SECOND() functions. The MySQL HOUR(), MINUTE() and SECOND() functions are a part of the date-time functions in MySQL.

Suppose you have a datetime or time value and you want to know what hour of the day it is, or what is the minute value or the second value. For instance, you have the time value – 06:45:22 and you want to know the hour is. You pass that time to the HOUR() function, it will return ‘6’ to you.

  • The HOUR() function is used to return the hour value from a given datetime or time value. 
  • The MINUTE() function is used to return the minute value from a given datetime or time value.
  • The SECOND() function is used to return the second value from a given datetime or time value.

Syntax of MySQL HOUR()

HOUR(value)Code language: SQL (Structured Query Language) (sql)

Where ‘value’ is a datetime or time value from which the value of the hour is to be found.

Syntax of MySQL MINUTE()

MINUTE(value)Code language: SQL (Structured Query Language) (sql)

Where ‘value’ is a datetime or time value from which the value of the minute is to be found.

Syntax of MySQL SECOND()

SECOND(value)Code language: SQL (Structured Query Language) (sql)

Where ‘value’ is a datetime or time value from which the value of the second is to be found.

Examples of MySQL HOUR()

Let us start with some basic examples to demonstrate the MySQL HOUR() function. Consider the below queries.

SELECT HOUR(“05:59:05”); 
SELECT HOUR(“16:06:15”); 
SELECT HOUR(“815:06:15”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Mysql Hour Basic Example

MySQL HOUR() With Datetime Value

Let us now pass a datetime value as a parameter to the HOUR() function. Consider the below queries.

SELECT HOUR(“2012-10-21 13:34:00”); 
SELECT HOUR(“2016-01-04 02:14:00”); 
SELECT HOUR(“2020-01-04 12:14:00”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Hour Datetime

MySQL HOUR() With Incorrect Datetime Values

When we pass incorrect datetime or time values to the HOUR() function, it returns NULL. An incorrect datetime value is month>12, day value greater than 31 and minute and second values greater than 59. Let us see a couple of queries for this.

SELECT HOUR(“2012-45-21 52:14:00”); 
SELECT HOUR(“2012-03-21 52:554:00”); 
SELECT HOUR(“2012-03-21 52:12:63”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Hour Wrong Dates

Examples of MySQL MINUTE()

Let us start with some basic examples to demonstrate the MySQL MINUTE() function. Consider the below queries.

SELECT MINUTE(“05:59:05”); 
SELECT MINUTE(“16:02:59”); 
SELECT MINUTE(“45:32:02”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MySQL Minute Basic Example

MySQL MINUTE() With Datetime Value

Let us now pass a datetime value as a parameter to the MINUTE() function. Consider the below queries.

SELECT MINUTE(“2021-02-14 20:11:00”); 
SELECT MINUTE(“2012-10-21 04:52:00”); 
SELECT MINUTE(“2012-10-21 00:34:00”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MySQL Minute Datetime

MySQL MINUTE() With Incorrect Datetime Values

When we pass incorrect datetime or time values to the MINUTE() function, it returns NULL. An incorrect datetime value is month>12, day value greater than 31 and minute and second values greater than 59. Let us see a couple of queries for this.

SELECT MINUTE(“2012-10-21 00:555:00”); 
SELECT MINUTE(“2020-45-21 05:06:00”); 
SELECT MINUTE(“2020-05-21 05:06:89”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Minute Wrong Dates

Examples of MySQL SECOND()

Let us start with some basic examples to demonstrate the MySQL SECOND() function. Consider the below queries.

SELECT SECOND(“05:59:05”); 
SELECT SECOND(“15:25:53”); 
SELECT SECOND(“22:25:16”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MySQL Second Basic Example

MySQL SECOND() With Datetime Value

Let us now pass a datetime value as a parameter to the SECOND() function. Consider the below queries.

SELECT SECOND(“2009-08-15 06:45:19”); 
SELECT SECOND(“2018-11-06 19:16:36”); 
SELECT SECOND(“2020-03-26 09:42:49”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MySQL Second Datetime

MySQL SECOND() With Incorrect Datetime Values

When we pass incorrect datetime or time values to the SECOND() function, it returns NULL. An incorrect datetime value is month>12, day value greater than 31 and minute and second values greater than 59. Let us see a couple of queries for this.

SELECT SECOND(“2009-08-82 06:45:19”); 
SELECT SECOND(“2016-10-22 06:96:23”); 
SELECT SECOND(“2016-10-22 20:12:960”);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Second Wrong Date

Using HOUR(), MINUTE(), SECOND() With Tables

Consider the below ‘BusStop’ table.

BusStop Table
BusStop Table

Let us write a query that displays the Bus Number and the hour, minute, and seconds of the departure time in separate columns of the result-set. We will make use of the SELECT statement, aliases and the HOUR(), MINUTE() and SECOND() function. The query is –

SELECT Bus_No, HOUR(DepartureTime) AS Hour, 
MINUTE(DepartureTime) AS Minute, 
SECOND(DepartureTime) AS Second FROM BusStop;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Busstop Table Example1

Conclusion

The MySQL HOUR(), MINUTE() and SECOND() functions are widely used while retrieving the values of hour, minute and second respectively from a time/datetime value. I would encourage you to practice different examples of it.

References