How to fetch data from specific hours in SQL-CodePudding

I have a problem with CosmosDB, which also allows some SQL queries.

I have a database that is being uploaded every hour with a new record. I would like to fetch everything from the last day from 8PM to 8AM current day and another query from 8AM to 8PM the current day. I have a timestamp in the db in ISO format. How the potential query (or queries) would look like?

I achieved to fetch last 24h like this, but I really want to stick to the time range 8PM-8AM and then 8AM-8PM, basically two shifts. Previous one and the current one.

function getLast24hTime(){
    var date = new Date();
    var a = date.setHours(-24);
    return a;
}

and then:

SELECT * FROM c where c.time >= udf.getLastHourTime()

tl;dr Everyday I want to fetch specific hours range from the previous day (8pm-8am) specific hours from the current day (8am-8pm). How.

CodePudding user response：

in mysql you could do

the other shift is analog

CREATE TABLE be (
  `date` DATETIME
);

INSERT INTO be
  (`date`)
VALUES
  ('2022-01-13 20:41:24'),
  ('2022-01-13 21:41:24'),
  ('2022-01-14 01:41:24'),
  ('2022-02-14 09:41:24'),
  ('2022-02-14 10:41:24');

SELECT `date` from be WHERE `date` >  CURDATE() - INTERVAL 1 DAY   INTERVAL 20 HOUR
ANd  `date` < CURDATE()    INTERVAL 8 HOUR

| date                |
| :------------------ |
| 2022-01-13 20:41:24 |
| 2022-01-13 21:41:24 |
| 2022-01-14 01:41:24 |

db<>fiddle here