Date_diff with specific condition time start and time end

is it possible to have date_diff with specific start and end time?

let say my store are open from 8AM - 10PM, which is 14 Hours. and I have开发者_JAVA百科 a lot of stuff to sell during that time. One of the SKU is out of stock from 2022-11-01 06.00 PM until tomorrow 2022-11-02 11.00 AM.

Instead of calculate 24 hours, I just want to calculate only from opening store until it closed or until its restock. Meaning from 6PM to 11AM is 8 Hours

my query

select date_diff('2022-11-02 11.00 AM', '2022-11-02 06.00 PM', hour) from table

with the result 17 hours instead of 8 hours

You should convert your dates to timestamps (TIMESTAMP(yourdate) or CAST(yourdate AS TIMESTAMP)) and use TIMESTAMP_DIFF instead.

This will allow you to work with smaller intervals than days.