How to select SQL TIMESTAMP values BETWEEN hour X AND hour Y-CodePudding

I have the following table. I would like to make a SELECT of only the rows where the hour value is between 09:00:00 and 21:00:00.

 --------------------- ----------- 
| timestamp_full      | value_a   |
 --------------------- ----------- 
| 2023-01-25 08:00:00 |        22 |
| 2023-01-25 08:30:00 |        93 |
| 2023-01-25 09:00:00 |        13 |
| 2023-01-25 09:30:00 |        52 |
| 2023-01-25 10:00:00 |        84 |
| ...                 |...        |
| 2023-01-25 20:00:00 |        31 |
| 2023-01-25 20:30:00 |        16 |
| 2023-01-25 21:00:00 |        57 |
| 2023-01-25 21:20:00 |        44 |
 --------------------- -----------

CodePudding user response：

The SQL hour() function returns the hour part for a given date.

SELECT * FROM table WHERE hour(timestamp_full) BETWEEN 09 AND 20;

returns a result set including values from 09:00:00 until 20:59:59

 --------------------- ----------- 
| timestamp_full      | value_a   |
 --------------------- ----------- 
| 2023-01-25 09:00:00 |        22 |
| 2023-01-25 09:00:01 |        93 |
| 2023-01-25 09:30:00 |        13 |
| 2023-01-25 10:30:00 |        52 |
| 2023-01-25 10:00:00 |        84 |
| ...                 |...        |
| 2023-01-25 20:00:00 |        31 |
| 2023-01-25 20:30:00 |        16 |
| 2023-01-25 20:59:59 |        57 |
 --------------------- -----------

The Oracle Documentation for SQL Built-In Functions lists other useful functions such as second(), minute(), hour(), day(), month(), year() and more.