Count overnight hours as one day

Question

I have a dataset where certain operations occur during the overnight hours which I'd like to attribute to the day before.

For example, anything happening between 2/23 8pm and 2/24 6am should be included in 2/23's metrics rather than 2/24. Anything from 6:01 am to 7:59pm should be counted in 2/24's metrics.

I've seen a few posts about decrementing time by 6 hours but that doesn't work in this case.

Is there a way to use an If function to specify that midnight-6am should be counted as date-1 rather than date without affecting the metrics for the 6am - 7:59pm hours?

Thanks in advance! Also, a SQL newbie here so apologies if I have lots of followup questions.

Answer 1

You can use date_add with -6 hours and then optionally cast the timestamp as a date.

create table t (dcol datetime);
insert into t values
('2022-02-25 06:01:00'),
('2022-02-25 06:00:00'),
('2022-02-25 05:59:00');

SELECT CAST(DATE_ADD(dcol, INTERVAL -6 HOUR)AS DATE) FROM t;

| CAST(DATE_ADD(dcol, INTERVAL -6 HOUR)AS DATE) |
| :-------------------------------------------- |
| 2022-02-25                                    |
| 2022-02-25                                    |
| 2022-02-24                                    |

db<>fiddle here

Answer 2

As said in the comments, your requirement is the occurrences in a 6 AM to 6 AM day instead of a 12-12 day. You can achieve this by decreasing the time by 6 hours as shown in @Kendle’s answer. Another way to do it is to use an IF condition as shown below. Here, the date is decremented if the time is before 6 AM on each day and the new date is put in a new column.

Query:

SELECT
 IF
   (TIME(eventTime) <= "06:00:00",
     DATE_ADD(DATE(eventTime), INTERVAL -1 DAY),
     DATE(eventTime)) AS newEventTime
FROM
 `project.dataset.table`
ORDER BY
 eventTime;

Output from sample data:

As seen in the output, timestamps before 6 AM are considered for the previous day while the ones after are considered in the current day.