Question:
i have a table called inputtable with columns equipment_id(int),telemetry_time(timestamp),i want to assign a row_number to the row from 8 am to 8am shift everyday. the below is the sample data link,INPUT TABLE
| equipment_id | telemetry_time |
|---|---|
| 2 | 2022-06-16 04:00:00 |
| 2 | 2022-06-16 04:30:00 |
| 2 | 2022-06-16 06:00:00 |
| 2 | 2022-06-16 06:30:00 |
| 2 | 2022-06-16 08:00:00 |
| 2 | 2022-06-16 08:30:00 |
| 2 | 2022-06-16 20:00:00 |
| 2 | 2022-06-16 23:30:00 |
| 2 | 2022-06-17 02:30:00 |
| 2 | 2022-06-17 03:00:00 |
| 2 | 2022-06-17 10:30:00 |
REQUIRED OUTPUT
| equipment_id | telemetry_time | Batch |
|---|---|---|
| 2 | 2022-06-16 04:00:00 | 1 |
| 2 | 2022-06-16 04:30:00 | 2 |
| 2 | 2022-06-16 06:00:00 | 3 |
| 2 | 2022-06-16 06:30:00 | 4 |
| 2 | 2022-06-16 08:00:00 | 1 |
| 2 | 2022-06-16 08:30:00 | 2 |
| 2 | 2022-06-16 20:00:00 | 3 |
| 2 | 2022-06-16 23:30:00 | 4 |
| 2 | 2022-06-17 02:30:00 | 5 |
| 2 | 2022-06-17 03:00:00 | 6 |
| 2 | 2022-06-17 10:30:00 | 1 |
i have added desired output in the below link with table name desiredtable https://www.db-fiddle.com/f/vhZbgTG8TcJ7SwqanoWEUL/4 please help me im new to SQL,
Answer:
Shift your times back by eight hours to get the right date:If you have better answer, please add a comment about this, thank you!