How the HOUR() function works in Mariadb?

The HOUR() function is a date and time function that returns the hour part of a time or datetime value.

Posted on

The HOUR() function is a date and time function that returns the hour part of a time or datetime value. It can be used to extract the hour component from a time or datetime value, or to perform calculations based on the hour value. The HOUR() function is compatible with the SQL standard and supports various data types, such as TIME, DATETIME, and TIMESTAMP.

Syntax

The syntax of the HOUR() function is as follows:

HOUR(time)

where time is the time or datetime value to be extracted. The function returns an integer value that represents the hour part of the time or datetime value. The value ranges from 0 to 23. If the time or datetime value is NULL, the function returns NULL.

Examples

Example 1: Extract the hour part from a time value

The following example uses the HOUR() function to extract the hour part from a time value.

SELECT HOUR('09:05:39') AS hour;

The function returns an integer value that represents the hour part of the time value. The output is:

+------+
| hour |
+------+
|    9 |
+------+

The hour part of the time value ‘09:05:39’ is 9.

Example 2: Extract the hour part from a datetime value

The following example uses the HOUR() function to extract the hour part from a datetime value.

SELECT HOUR('2024-02-08 09:05:39') AS hour;

The function returns an integer value that represents the hour part of the datetime value. The output is:

+------+
| hour |
+------+
|    9 |
+------+

The hour part of the datetime value ‘2024-02-08 09:05:39’ is 9.

Example 3: Extract the hour part from a timestamp value

The following example uses the HOUR() function to extract the hour part from a timestamp value.

SELECT HOUR('2024-02-08T09:05:39Z') AS hour;

The function returns an integer value that represents the hour part of the timestamp value. The output is:

+------+
| hour |
+------+
|    9 |
+------+

The hour part of the timestamp value ‘2024-02-08T09:05:39Z’ is 9.

Example 4: Calculate the difference between two hour values

The following example uses the HOUR() function to calculate the difference between two hour values.

SELECT HOUR('12:00:00') - HOUR('09:30:00') AS difference;

The function returns an integer value that represents the difference between the hour parts of the two time values. The output is:

+------------+
| difference |
+------------+
|          3 |
+------------+

The difference between the hour parts of the two time values ‘12:00:00’ and ‘09:30:00’ is 3.

Some of the functions that are related to the HOUR() function are:

  • MINUTE(): This function returns the minute part of a time or datetime value. It has the same syntax and behavior as the HOUR() function. For example, MINUTE('09:05:39') returns 5.
  • SECOND(): This function returns the second part of a time or datetime value. It has the same syntax and behavior as the HOUR() function. For example, SECOND('09:05:39') returns 39.
  • EXTRACT(): This function extracts a specific part of a date or time value. It has a different syntax and behavior from the HOUR() function. For example, EXTRACT(HOUR FROM '09:05:39') returns 9.

Conclusion

The HOUR() function is a useful function to extract the hour part of a time or datetime value. It supports various data types and follows the SQL standard. It can be used to extract the hour component from a time or datetime value, or to perform calculations based on the hour value. It has some related functions that have similar or different functionalities. The HOUR() function is a powerful tool for date and time analysis and manipulation in Mariadb.