I am trying to calculate the worked hours for specific days in Google BigQuery (SQL).

The pay wage is $10 when you work on a day time but $15 when you work on a night time.
Day time is defined as 6am to 10pm whereas night time is defined as 10pm to 6am.

Employees can work flexibly as they are limousine drivers.

The following is an example of my table:

| id | start\_at | end\_at | date |
| --- | --- | --- | --- |
| abc123 | 04:00:00 | 07:00:00 | 2020-01-05 |
| abc123 | 09:00:00 | 15:32:00 | 2020-01-05 |
| abc123 | 23:00:00 | 23:35:00 | 2020-01-05 |
| abc123 | 23:40:00 | 23:59:00 | 2020-01-05 |
| abc123 | 23:59:00 | 01:35:00 | 2020-01-05 |
| abc123 | 02:02:00 | 04:35:00 | 2020-01-06 |
| abc123 | 05:40:00 | 06:59:00 | 2020-01-06 |

So the actual work hours is calculated by taking the difference between start\_at and end\_at but the day time and night time conditions are becoming a hassle in my query..

\*the date column is based on start\_at. Even when you start at 11:59pm and end at the next day 12:05am, the date follows the date of the start\_at instead of end\_at.

Any ideas? Thanks in advance!