BigQuery retrieve all rows where timestamp is n minutes before or after a given time of the day-CodePudding

I'm trying to write a query that takes two arguments:

A time of the day TIME(0, 0, 0)
A number of minutes INTERVAL 3 MINUTE

And returns all rows where startTime is within the period defined as the time of the day before and after the number of minutes.

The query below works well, except when the time is midnight in which case nothing is returned.

How can I re-write this query to correctly handle this edge case, such that given above values I would get entries for 23:57, 23:58, 23:59, 00:00, 00:01, 00:02 and 00:03?

SELECT *
FROM data-warehouse.exchange.OHLCV_future_1m
WHERE startTime BETWEEN
  DATETIME(DATE(startTime), TIME(0, 0, 0)) - INTERVAL 3 MINUTE AND
  DATETIME(DATE(startTime), TIME(0, 0, 0))   INTERVAL 3 MINUTE
LIMIT 100;

Edit 1: startTime is type DATETIME, and the data is at a minute frequency.

Edit 2: added screenshot of startTime

Edit 3: The following query returns 23:56:00, 23:57:00, 23:58:00 and 23:59:00

SELECT *
FROM data-warehouse.exchange.OHLCV_future_1m
WHERE startTime BETWEEN
  DATETIME(DATE(startTime), TIME(23, 59, 0)) - INTERVAL 3 MINUTE AND
  DATETIME(DATE(startTime), TIME(23, 59, 0))   INTERVAL 3 MINUTE
LIMIT 100;

CodePudding user response：

You wabt a three Minute interval for a flexible time point.

So xou should check, if the time point is midnight and remove 1 Day

SELECT *
FROM data-warehouse.exchange.OHLCV_future_1m
WHERE startTime BETWEEN
(CASE WHEN  TIME(0, 0, 0) =  TIME(0, 0, 0) THEN
    DATETIME(DATE(startTime), TIME(0, 0, 0)) - INTERvVAL 1 DAY - INTERVAL 3 MINUTE 
  ELSE
    DATETIME(DATE(startTime), TIME(0, 0, 0))  - INTERVAL 3 MINUTE 
  END)
  AND
  DATETIME(DATE(startTime), TIME(0, 0, 0))   INTERVAL 3 MINUTE
LIMIT 100;

CodePudding user response：

Not heavily tested but hope below gives you some direction.

WITH sample_table AS (
  SELECT * FROM UNNEST([
    DATETIME '2022-01-12 23:56:00', '2022-01-28 23:59:00', '2022-01-27 00:02:00',
             '2022-01-27 00:03:00', '2022-01-28 00:00:00', '2022-11-28 23:57:00',
             '2022-03-05 03:46:00', '2022-03-05 15:28:00', '2022-03-05 20:30:00'
  ]) startTime
)
SELECT *, time_of_the_day, interval_in_minutes,
  FROM sample_table
 WHERE DATETIME(DATE(startTime), time_of_the_day) BETWEEN startTime - interval_in_minutes AND startTime   interval_in_minutes
    OR DATETIME(DATE(startTime), time_of_the_day)   INTERVAL 1 DAY BETWEEN startTime - interval_in_minutes AND startTime   interval_in_minutes;

Query results